Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
will it work if I use Date>#29/01/2012#
Try:
where Date > Date('29/01/2012','MM/DD/YYYY')
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!
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
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;
you can use any format
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'
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.