topic Date format question in QlikView

Date format question

Fri, 15 Apr 2011 15:54:55 GMT

Hi,

I have loaded 2 tables in via SQL and the date 14/02/11 appear as 40588 & 40588.001.

I have tried to force them to be the same using date(value,'DD/Mm'YY'), I now have 14/02/11 appearing in a listbox twice.

Any help or explanation would be nice.

Thinkyou,

Gav.

Date format question

Fri, 15 Apr 2011 15:59:32 GMT

I would add distinct after the load statements or... round the date with the .001 in the script but probably just use the distinct statement.

Date format question

Fri, 15 Apr 2011 16:08:39 GMT

trent.jones

Thank you the round worked. Do you know why the round worked and the force of the date did not, or why one of the dates has a .001 after it ?

Thank you once again this has been driving me mad .....

Date format question

Miguel_Angel_Baeyens — Fri, 15 Apr 2011 16:20:08 GMT

Hello,

If the date contains decimals, which is possible, is because for some reason, the date is not just "a date" rather than a timestamp. Those decimals are the hours or minutes of the timestamp. Using

Floor(Date(value,'DD/MM/YY'))

Will leave the date as an integer, regardless the time of the day.

It appears twice because Date() is a representation function, but the underlying numeric value is still different ('14/02/11 00:00:00' and '14/02/11 00:01:26').

Using the Floor() will avoid that, as all values in the same date will not have hours or minutes.

Hope that helps.