seperate data and time from the dateTimestamp

[markgraham123]

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

[sunny_talwar · Accepted solution]

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

...

];

[sunny_talwar]

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

...

];

[markgraham123]

Thanks a lot Sunny

[sunny_talwar]

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.

[markgraham123]

Perfect brother!

This would help me to understand more!!!