topic Re: seperate data and time from the dateTimestamp in QlikView

seperate data and time from the dateTimestamp

markgraham123 — Fri, 06 May 2016 17:21:57 GMT

Hi All,

I'm trying to seperate date and time into 2 diff. fields form DateTimeStamp value.

EG:

8/31/15 1:00 AM

into

MyDate

8/31/15

MyTime

1:00 AM

Pls find attached applciation

Re: seperate data and time from the dateTimestamp

sunny_talwar — Fri, 06 May 2016 17:24:54 GMT

Try this:

LOAD *,

Date(Floor(TimeStamp)) as Date,

Time(Frac(TimeStamp)) as Time;

LOAD TimeStamp#(Test, 'M/D/YY h:mm TT') as TimeStamp,

Test;

LOAD * Inline

[

Test

8/30/15 8:50 PM

8/30/15 11:40 PM

8/31/15 12:15 AM

...

];

Re: seperate data and time from the dateTimestamp

sunny_talwar — Fri, 06 May 2016 17:27:40 GMT

Or this:

SET TimeFormat='h:mm TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YY h:mm TT';

LOAD *,

Date(Floor(Test)) as Date,

Time(Frac(Test)) as Time;

LOAD * Inline

[

Test

8/30/15 8:50 PM

8/30/15 11:40 PM

8/31/15 12:15 AM

...

];

Re: seperate data and time from the dateTimestamp

markgraham123 — Fri, 06 May 2016 18:12:03 GMT

Thanks a lot Sunny

Re: seperate data and time from the dateTimestamp

sunny_talwar — Fri, 06 May 2016 18:13:56 GMT

Not a problem Mark. I would suggest going through this link to understand why QlikView is unable to understand dates sometime. Why don’t my dates work?‌ and Get the Dates Right‌. Once you will have this concept cleared out, you will never have any issues with dates.

Re: seperate data and time from the dateTimestamp

markgraham123 — Fri, 06 May 2016 18:18:12 GMT

Perfect brother!

This would help me to understand more!!!