Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

markgraham123
Contributor II

seperate data and time from the dateTimestamp

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

Tags (2)
1 Solution

Accepted Solutions

Re: seperate data and time from the dateTimestamp

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

...

];

Capture.PNG

5 Replies

Re: seperate data and time from the dateTimestamp

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

...

];

Capture.PNG

Re: seperate data and time from the dateTimestamp

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

...

];


Capture.PNG

markgraham123
Contributor II

Re: seperate data and time from the dateTimestamp

Thanks a lot Sunny

Re: seperate data and time from the dateTimestamp

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

Re: seperate data and time from the dateTimestamp

Perfect brother!

This would help me to understand more!!!

Community Browser