How to get correct date

Report Inappropriate Content · ‎2014-01-23

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.

Report Inappropriate Content · ‎2014-01-23

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

Report Inappropriate Content · ‎2014-01-23

Try:

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

Carlos_Reyes · ‎2014-01-23

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!

Report Inappropriate Content · ‎2014-01-23

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

Anonymous · ‎2014-01-23

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;

Report Inappropriate Content · ‎2014-01-23

you can use any format

Report Inappropriate Content · ‎2014-01-23

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

Colin-Albert · ‎2014-01-23

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'

Anonymous · ‎2014-01-23

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.

Related Topics