topic Re: Making timestamp adds days to date in QlikView

Making timestamp adds days to date

dafnis14 — Tue, 24 May 2016 07:39:19 GMT

Hi,

based on other discussions, I used the following expression to create a timestamp

based on a date and an hour from different fields.

Timestamp(RsvLineCreateDate+ [RsvLine Create Hour], 'DD/MM/YY hh:mm')

But the final date in the timestamp is always 2 days later then the basis date.

for example:

date: 07/11/2011

time:07:51:00

final timestamp: 09/11/2011 07:51

Any idea why it happens?

Thanks!

Re: Making timestamp adds days to date

jonathandienst — Tue, 24 May 2016 07:42:13 GMT

What (actual numeric) values do the two fields contain? It seems that one of them is not what you think.

Re: Making timestamp adds days to date

Kushal_Chawda — Tue, 24 May 2016 07:43:57 GMT

can show the screenshot of RsvLineCreateDate, [RsvLine Create Hour] in list box?

Re: Making timestamp adds days to date

dafnis14 — Tue, 24 May 2016 07:50:58 GMT

It seems that the issue is in the time field:

The expression =num([RsvLine Create Hour]) yields:

2.3270833333372

so the 2 days are actually hidden in the time field..

Displaying the time field as timestamp shows:

01/01/1900 07:51:00

How does this happen???

Thanks!

Re: Making timestamp adds days to date

Kushal_Chawda — Tue, 24 May 2016 07:55:08 GMT

instead doing it using + operator, format it using Timestamp function like below

= Timestamp(Timestamp#(RsvLineCreateDate & [RsvLine Create Hour],'MM/DD/YYYYhh:ss'),'DD/MM/YYYY hh:mm')

Re: Making timestamp adds days to date

sasiparupudi1 — Tue, 24 May 2016 08:14:03 GMT

I think you need to convert the fields into date and time internal values before you add them.

ex:

= timestamp(date#('07/11/2011','DD/MM/YYYY') +time#('07:51:00','hh:mm:ss'),'DD/MM/YYYY hh:mm:ss')

hth

Sasi

Re: Making timestamp adds days to date

dafnis14 — Tue, 24 May 2016 08:14:59 GMT

I thought it would be " healthier" to do the following:

Timestamp(Num((RsvLineCreateDate)+num(MakeTime(Hour([RsvLine Create Hour])))),'DD/MM/YYYY hh:00')

Looks messy but re solved the issue... 🙂

Re: Making timestamp adds days to date

dafnis14 — Tue, 24 May 2016 08:54:17 GMT

Thanks!

Re: Making timestamp adds days to date

MarcoWedel — Tue, 24 May 2016 22:32:51 GMT

Hi,

another possibility to get rid of the (integer) day part of your time field:

Timestamp(RsvLineCreateDate + Frac([RsvLine Create Hour]), 'DD/MM/YY hh:mm')

hope this helps

regards

Marco