Date format question

[#1]

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.

[#2 · Accepted solution]

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.

[#3]

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 .....

[Miguel_Angel_Baeyens]

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.