How to get correct date

[#1]

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.

[Clever_Anjos]

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

[Anonymous]

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?

[Clever_Anjos]

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')

or

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

[Colin-Albert]

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.

[#6]

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