topic Re: How to convert timestamp into Time and Hour in QlikView

How to convert timestamp into Time and Hour

Wed, 25 Nov 2020 16:16:04 GMT

All helps and tips I've found were complicated to understand (for me !). So here is a very easy example.

In my DB I have CDATETIME = 3671638531991000 ==> from a Firebird DB

In your Qlik upload Script :

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]'; <=Default Qlik

in a Load expression :

Date(Timestamp(floor(CDATETIME / 86400000000))) as Datetmp, ==> returns "05/05/2016 00:00:00"

time(Frac(CDATETIME / 86400000000)) as Timetmp, ==> returns "19:35:32"

Hope this will help.

vishsaggi — Wed, 08 Mar 2017 16:46:30 GMT

What is that you want to display for Timetmp field?

May be this?

Timestamp(Frac(CDATETIME / 86400000000), 'hh:mm') AS Timetmp; --> Returns 19:35

Wed, 08 Mar 2017 16:52:13 GMT

Sorry, it wasn't a question but a tip.

vishsaggi — Thu, 09 Mar 2017 16:12:53 GMT

Oh ok. No problem.

avinashelite — Thu, 09 Mar 2017 16:19:10 GMT

Your using to different function that's why our seeing the change

frac returns the decimal part

frac(3671638531991000/86400000000) = 0.816342

floor eliminated the decimal part

floor(3671638531991000/86400000000) = 42495

because of which in the first case you get the time as 00 and get proper time in second case

Hope you got the answer