Creating a timestamp from time and date correct it.

[qlikconsultant]

I have in an excel a date and time in 2 fields. I want to correct the date to the next day if the time is betwean 00:00:00 and 00:04:00.

As first i try to create a timestamp.

my_data:

load

date(mydate),

time(mytime),

date(mydate) &time(mytime) as my_Time_timestamp

FROM

Data.xlsx;

But as result i get an empty fields for my_Time_timestamp. What did I wrong?

[hic]

There could be many things.

First, check if a Load mydate, mytime From Excel interprets the fields as numbers (which it should). If the field values are right-aligned, then they are probably correctly interpreted.

If not, you need to use the date#() and time#() functions.

Once they are correctly interpreted, you should create the timstamp using

timestamp(date#(mydate) + time#(mytime))

HIC

[qlikconsultant · Accepted solution]

I used this solution:

if(time(mytime<'04:00:00'), date(mydate+ 1), date(mydate)) as my_Time_timestamp,

[Anonymous]

I usually use this solution but sometimes I need to force the date format as:

timestamp(date#(date(mydate)) + time#(time(mytime)))

Ag+