Truncate not possible in qlikview or how to do?

[#1]

Ok i have a lot of dates from different tables loaded from SQL into QVD (they need to stay the default like in the SQL table for other reports).

Example from dates i currently have

1. 40099                          a timestamp witout hours, minutes and seconds. only Day's
2. 40099,435405093         a timestamp with hours, minutes and seconds
3. 16/08/2012                  a date
4. 16/08/2012 12:55:23     a date with hours, minutes and seconds

Now i want them all to be like the first Example (40099) and they need to be Truncated like in sql (The hours, minutes and seconds need to be gone forever). But Qlikview has no Truncate function.

How can i convert 1, 2 and 3 to 1 with no (hidden)hours, minutes and seconds annymore?

Thanks a lot!

[hic]

The Floor() function does exactly this.

HIC

[#3]

Can you explain me exactly how?

I also found out that the left(Date, 5) as Date can help a lot.

[hic · Accepted solution]

Dates and timestamps are internally stored as numbers, so it is just a matter of truncating the number to an integer. Then you will have what you want.

You can load your examples using the following formulae

1. Floor(Timestamp) as Daynumber
2. Floor(Timestamp) as Daynumber
3. Floor(Date#(Date,'DD/MM/YYYY')) as Daynumber
4. Floor(Date#(TimeStamp,'DD/MM/YYYY hh:mm:ss')) as Daynumber

And if you have a mixture, you can use the following:

Floor(Alt(

     Date#(x,'DD/MM/YYYY hh:mm:ss'),

     Date#(x,'DD/MM/YYYY'),

     Num#(x,'#',',','.')

))

Yes, you can use string functions, but in my experience you get other problems then: You sometimes lose the dual/numeric property; you have no test that the date has been correctly interpreted, etc. So I always avoid string functions for date/time fields.

HIC