topic Re: How to get correct date in QlikView

How to get correct date

Thu, 23 Jan 2014 15:08:47 GMT

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.

Re: How to get correct date

Thu, 23 Jan 2014 15:19:52 GMT

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

Re: How to get correct date

Thu, 23 Jan 2014 15:26:23 GMT

Try:

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

Re: Re: How to get correct date

Carlos_Reyes — Thu, 23 Jan 2014 15:32:31 GMT

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!

Re: How to get correct date

Thu, 23 Jan 2014 15:36:44 GMT

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

Re: How to get correct date

Anonymous — Thu, 23 Jan 2014 15:44:21 GMT

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;

Re: How to get correct date

Thu, 23 Jan 2014 15:46:46 GMT

you can use any format

Re: How to get correct date

Thu, 23 Jan 2014 15:46:55 GMT

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

Re: How to get correct date

Colin-Albert — Thu, 23 Jan 2014 15:53:51 GMT

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'

Re: How to get correct date

Anonymous — Thu, 23 Jan 2014 17:43:50 GMT

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.

Re: How to get correct date

Clever_Anjos — Thu, 23 Jan 2014 17:47:40 GMT

SQL Databases (Oracle, Microsoft, etc) usually uses 'YYYY-MM-DD' format. A column can be Date OR DateTime.

Dates and datetimes into QV are stored as decimals numbers properly formatted

23/01/2014 15:47:22 = 41662,658032407

Re: How to get correct date

Anonymous — Thu, 23 Jan 2014 17:54:29 GMT

So on the SQL side I should use Where >= '2010-04-01';

Instead of Where >= '04-01-2010'; and I should get the proper result?

I also assume QlikView is working because it is storing date as a # so formatting is not an issue?

Re: How to get correct date

Clever_Anjos — Thu, 23 Jan 2014 17:59:24 GMT

select SQL * from Table where Date > '2010-04-01' should work on any database.

I´d rather use conversion from specific database

Eg: Oracle

select SQL * from Table where Date > to_date('2010-04-01','YYYY-MM-DD')

select SQL * from Table where Date > to_date('04-01-2010','MM-DD-YYYY')

QlikView store dates(and timestamps) as decimal numbers.

Make a simple test:

Add a text box to any application and add =now() as expression, check what it returns

then change to = num(now()) and check again

Re: How to get correct date

Colin-Albert — Thu, 23 Jan 2014 18:09:08 GMT

Dates are stored by default as a datetime value in a dual field with the date part as the integer and the fraction as the time component, along with a text representation of the date.

It is more efficient in QlikView to separate these values into two fields, as storing dates as integers and time separately can significantly reduce the number of unique data values. See below.

Use date(floor(datetime_field)) as date_value to store just the date component

and time(frac(datetime_field)) as time_value to store just the time component

Displaying a date or time field using the formatting functions e.g. date(date_value, 'YYYY-MMM') only changes the text portion of the dual data value it does not change the underlying numeric portion, so each separate date or datetime value still exists. This can cause QlikView to display the same text value multiple times and not aggregate all the values to a single column, if you are not aware of how the dual values are affected.

Re: How to get correct date

Thu, 23 Jan 2014 18:50:00 GMT

Hi,

First thing is to see if Date field in SQL environment is a date, a timestamp, or a string. Do you see it as '29/01/2012' in base? Then check the Date format of Qlikview, to see if it's the same. What works for me:

If Date is a normal date: should work: Where Date>to_date('29/01/2012','DD/MM/YYYY')

If it's a timestamp, you need: Where trunc(Date)>to_date('29/01/2012','DD/MM/YYYY')

If it's a string: Where to_date(Date,'DD/MM/YYYY')>to_date('29/01/2012','DD/MM/YYYY')

Hope I fell on the right one