topic Re: Extract datetime as seperate date & time. in QlikView

Extract datetime as seperate date & time.

userid128223 — Thu, 06 Jun 2013 19:27:16 GMT

Hi Everyone

I have date & time numeric field ( i am assuming it is numeric because it is alligned right when i view it in list box)

2013-01-01 00:00:00

2013-01-01 01:00:00

2013-01-01 02:00:00

2013-01-01 03:00:00

I am trying to extract date & time. Date is working fine, however time stamp is comming out incorrectly. below is what i am using.

date(floor(datefiled),'YYYY-MM-DD') as NewDate,

time(frac(datefield),'hh:mm:ss') as NewTime,

NewTime extracted shows up like this for above 4 field.

datefield NewTime

2013-01-01 00:00:00 08:00:00

2013-01-01 01:00:00 07:00:00

2013-01-01 02:00:00 17:00:00

2013-01-01 03:00:00 18:00:00

Why do my time extract incorrectly?

Re: Extract datetime as seperate date & time.

rustyfishbones — Thu, 06 Jun 2013 19:32:49 GMT

What about just

Re: Extract datetime as seperate date & time.

userid128223 — Thu, 06 Jun 2013 19:36:20 GMT

using Time only function gives me this

2013-01-01 00:00:00 17:00:00

2013-01-01 01:00:00 18:00:00

2013-01-01 02:00:00 19:00:00

2013-01-01 03:00:00 20:00:00

Re: Extract datetime as seperate date & time.

shree909 — Thu, 06 Jun 2013 19:39:19 GMT

Hi try like this

=subfield('2013-01-01 01:00:00 ',' ',2) result 01:00:00

=subfield(Date,' ',2) AS New_Time

Re: Extract datetime as seperate date & time.

rustyfishbones — Thu, 06 Jun 2013 23:40:08 GMT

Add the date field in a list box and choose number format as timestamp

Let me know what result that gives you

You should see 00:00:00

Re: Extract datetime as seperate date & time.

MayilVahanan — Fri, 07 Jun 2013 02:04:15 GMT

Try like this

Test:

Load Time(Date) as Time,*;

LOAD Date(Date#(DateTime,'YYYY-MM-DD hh:mm:ss'),'YYYY-MM-DD hh:mm:ss') AS Date;

Load * Inline

[

DateTime

2013-01-01 00:00:00

2013-01-01 01:00:00

2013-01-01 02:00:00

2013-01-01 03:00:00

];

Hope it helps

PFA