Extract datetime as seperate date & time.

[userid128223]

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?

[rustyfishbones]

What about just

[userid128223]

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

[shree909]

Hi  try like this

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

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

[rustyfishbones]

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

[MayilVahanan]

HI

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