Date field that includes day and time stamp

[#1]

Dear QV users,

I have problems with extracting a date stamp in a field that has a date and time stamp. The field is called RealizedIBReady. I included a table as an attachment.

I tried the MakeDate function:

MakeDate(Mid(left(RealizedIBReady,4),7,4),Mid(left(RealizedIBReady,4),4,2),Mid(left(RealizedIBReady,4),1,2)) as RealizedIBReadyDay

Data is extracted from a QVD file. I have imported the field with a left join into the exciting file.

regards,

Aissam

[Gysbert_Wassenaar · Accepted solution]

Qlikview recognized the RealizedBReady field automatically as a timestamp value when I loaded the excel file. No conversion was necessary.

If you want to cut off the time part of a timestamp value you can use the floor function: floor(MyTimeStampField). That returns a number that represents the date, so you may want to format it as a date with the date() function: date(floor(MyTimeStampField), 'DD-MM-YYYY')

[#3]

Hello Gysbert,

That's true. The problem I have is that you have a date and a time stamp. If the same date has different timestamps, the line chart shows this separate instead as a total for a whole day.

regards,

Aissam

[PrashantSangle]

Hi,

But if you use Floor() as Gysbert Suggest. YOu no need to worry about time stamp.

Regards

[its_anandrjs]

Hi,

Your date field RealizedIBReady is in proper timestamp date format also try to load

LOAD JOB_NUMBER,

RealizedIBReady,

Timestamp( (RealizedIBReady) ) as TestRealizedIBReady,

Year( Timestamp( (RealizedIBReady) )) as Year,

Month( Timestamp( (RealizedIBReady) )) as Month,

Day( Timestamp( (RealizedIBReady) )) as Day,

MakeDate(Year( Timestamp( (RealizedIBReady) )),

Month( Timestamp( (RealizedIBReady) )),

Day( Timestamp( (RealizedIBReady) ))) as Date

FROM

[14-1022 tabel date stamp.xlsx]

(ooxml, embedded labels, table is Sheet1);

Regards,

Anand