Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
dafnis14
Specialist
Specialist

Making timestamp adds days to date

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!

8 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Kushal_Chawda

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

dafnis14
Specialist
Specialist
Author

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!

Kushal_Chawda

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')

sasiparupudi1
Master III
Master III

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

dafnis14
Specialist
Specialist
Author

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

dafnis14
Specialist
Specialist
Author

Thanks!

MarcoWedel

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