Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to get correct date

Hi,


When I am loading data from ODBC drive i,e from access database it is showing data type mismatch error


the query is


Load a,b,c

select SQL * from Table where Date>'29/01/2012';


Can anyone help me as I tried in many date formats but nothing works for me please help me as this is urgent now please.


Thanks.

14 Replies
Not applicable

will it work if I use Date>#29/01/2012#

Not applicable

Try:

where Date > Date('29/01/2012','MM/DD/YYYY')

Partner
Partner

It is a common error with dates, you have to use the same format that your database field uses, but I usually do something like the next:

// I create a variable with de date

LET vLimitDate = MakeDate(2012,01,29);

//I use the variable in the SQL query

WHERE TRUNC ( YourDatabaseFieldName ) > TODATE( '$(vLimitDate)','DD/MM/YYYY')

Of course, you have to use the correct functions for your database.

Hope it helps!

Not applicable

thanks and earlier I used #09/01/2012# it worked fine . Now I just confused how to give 29/01/2012.

please let me know the format which I have to use whether DD/MM/YYYY  or MM/DD/YYYY

Creator III
Creator III

Pull Date into the QlikView script and use where there.

Load a,b,c,Date

Where Date>'29/01/2012';

select SQL * from Table;

Drop Field Date;

Not applicable

you can use any format

Not applicable

I tried it but it is not working as I have the source from access database.

Putting the where clause on the QlikView side of the query is inefficient as all data is being accessed in the SQL query and it is filtered afterwards.

Also using select * means that all data fields are being accessed and will also degrade performance when only a few fields are used by QlikView.

Filter the data in the SQL query and only load the required fields from SQL will improve load performance, often giving significant improvements.

Regards date formats, using 'YYYY-MM-DD' often solves these issues as there is no ambiguity in the sequence of days and months.

Try

Load a,b,c

select SQL * from Table where Date > '2012-01-29'

Creator III
Creator III

Thanks for the advise Colin.  You seem to be pretty knowledgeable about this topic.

Is it possible that a field in SQL could have both Date and DateTime?  I have a case when I put the where statment within the SQL query I do not get the proper result vs when I put the where on the QlikView side.  I suspect it has something to do with the way QlikView handles dates but I'm not entirely sure.