topic Re: Problem separating Date and Time in QlikView

Problem separating Date and Time

gshockxcc — Tue, 10 Feb 2015 12:24:07 GMT

I found several other posts regarding the separation of date and time from a single field. I implemented a method that appears to work, but only captures a few dates from all the available data.

Any help would be appreciated.

Please see attached file. Thanks.

Re: Problem separating Date and Time

datanibbler — Tue, 10 Feb 2015 12:28:13 GMT

Hi Kristan,

your DateTime field seems to be a Timestamp?

That is saved internally as a numeric value (decimal value). So you can use the Floor() and Frac() functions to split it - Floor() will get the day and Frac() will get the time out of that.

Then you can use the DATE() or TIME() functions to format it and, for instance, have only the hours (out of the time) displayed.

HTH

Best regards,

DataNibbler

Re: Problem separating Date and Time

rubenmarin — Tue, 10 Feb 2015 12:29:23 GMT

Hi Kristan, in subfield funtion you have to use [Date Time] instead of DateTime.

Re: Problem separating Date and Time

tresB — Tue, 10 Feb 2015 12:34:32 GMT

Try like:

Load

Date(TimeStamp#(DateTime, 'MM/DD/YYYY hh:mm:ss')) as Date,

Hour(TimeStamp#(DateTime, 'MM/DD/YYYY hh:mm:ss')) as Hour,

Minute(TimeStamp#(DateTime, 'MM/DD/YYYY hh:mm:ss')) as Minute,

Second(TimeStamp#(DateTime, 'MM/DD/YYYY hh:mm:ss')) as Second

Re: Problem separating Date and Time

gshockxcc — Tue, 10 Feb 2015 12:35:26 GMT

Ruben,

Perfect. That small change, and it worked like a charm. Exactly what I needed. Thank you.