Date issue while extracting data .

[#1]

Dear Experts ,

I am trying to extract data from below table . when i remove date filter from where condition its working fine , where when i add date filter its displaying zero records found .

when i execute same query in back end (sql tool) i am getting 1 million record .

Please suggest, i am doing anything wrong in adding date filter , else data format is wrong in my condition .

TransactionFact :

SQL SELECT  did,document_date from fas_gl_transaction where company_code = 'TEST' and trunc(document_date) between '01-APR-2013' and '31-MAR-2014';

STORE TransactionFact into ..\QVD\TEST\TransactionFact_History_2013.QVD;

[Peter_Cammaert · Accepted solution]

In his OP he said:

when i execute same query in back end (sql tool) i am getting 1 million record

which makes it a very weird issue because the execution is done in both cases by the database engine. Could it be permission-related? Seems improbable...

Sriram, try rewriting or removing parts of the WHERE condition in your SELECT statement. For example, it could be that the database doesn't understand the between date values anymore in your QlikView script context. Can you rewrite them as default database dates? Like

:

WHERE trunc(document_date) between '2013-04-01' and '2014-03-31'

:

Best,

Peter

[#3]

Dear Peter ,

Thanks a lot , Now its fetching data after changing date format as per your reply . i am using oracle oledb connection to extract record , where i never thought this date format will create such a issue . could you please tell me whats a difference between using date format in (DD-MON-YYYY)  and (YYYY-MON-DD)

[Peter_Cammaert]

The standard database date format isn't YYYY-MON-DD, it rather is YYYY-MM-YY meaning that all fields are numeric.

There isn't much of a difference except that your format may not work for various reasons (language/regional settings is one that comes to mind - month names are different in different languages), while the database date format YYYY-MM-YY will always work. Especially when working with Oracle.

Best,

Peter

[sunny_talwar]

Peter not really sure how all this work. But when we have to restrict dates while grabbing stuff from Oracle, we use DD-MMM-YYYY

UPDATE: I guess the only difference is the casing. I would have used this:

Trim(document_date) between '01-Apr-2013' and '31-Mar-2014';

vs.

Trim(document_date) between '01-APR-2013' and '31-MAR-2014';

Not sure if that make any difference.

[Peter_Cammaert]

Sunny, feel free to use whatever works best for you.

[sunny_talwar]

Peter -

I am sorry if you found my response to be offensive. But it was more of a question for you. I will have to use what has been told by the SQL guys. But do you know why is it working with MMM in my case? If you don't know, then that is fine

Best,

Sunny

[Peter_Cammaert]

Nah, not offensive at all. Of course not.

I don't know why short month names are working for you. And whatever I say, the short month names appear not to work in Sriram's QlikView context. In my experience, standard database dates work best and consistently across DBMS products. I admit I haven't tested them all.

So if it works for you, go ahead and use those date formats. Especially when the SQL gurus have a final say in what you should and what you shouldn't use. That's at least one thing less to worry about

Take care,

Peter.