Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

dafnis14
Contributor III

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

Re: Making timestamp adds days to date

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

Re: Making timestamp adds days to date

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

dafnis14
Contributor III

Re: Making timestamp adds days to date

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

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
Honored Contributor III

Re: Making timestamp adds days to date

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
Contributor III

Re: Making timestamp adds days to date

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
Contributor III

Re: Making timestamp adds days to date

Thanks!

Re: Making timestamp adds days to date

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

Community Browser