topic Re: Date time stamp conversion: YYYY/MMDD HH:MM:SS in QlikView

Date time stamp conversion: YYYY/MMDD HH:MM:SS

isciberras — Wed, 25 Nov 2020 16:16:04 GMT

Hi Everyone,

I'm having trouble with converting a time stamp and can't seem to find the solution to this online.

I'm have a timestamp field in the format of: YYYY/MM/DD HH:MM:SS and I would like to separate the date and time fields from this. I tried several different types of syntax to get the required answer however nothing seems to work. So far the result of trying to convert the date timestamp is Qlikview not loading in the field.

Does anyone know how I can solve this issue?

Thanks,

Isaac

Re: Date time stamp conversion: YYYY/MMDD HH:MM:SS

Anonymous — Fri, 10 Nov 2017 12:25:24 GMT

Do you have an example app / data you could share so we can see how it's been setup?

Re: Date time stamp conversion: YYYY/MMDD HH:MM:SS

Colin-Albert — Fri, 10 Nov 2017 12:29:41 GMT

My guess is that you are not loading the timestamp values as a correctly formatted datetime so the data is stored in Qlik as a numeric value.

These posts should help.

Why don’t my dates work?

Get the Dates Right

Once you have the timestamp data in the correct format using time(frac([YourTimeStampField])) and date(floor([YourTimeStampField])) will separate the date and time components

Re: Date time stamp conversion: YYYY/MMDD HH:MM:SS

PrashantSangle — Fri, 10 Nov 2017 13:00:09 GMT

Hi,

try with subfield(datefield,' ',1) as date

subfield(datefield,' ',2) as time

also check timestamp#(),timestamp(),date() and date#() in help menu

syntax and explanation is available in help

Regards,

Prashant

Re: Date time stamp conversion: YYYY/MMDD HH:MM:SS

Anonymous — Fri, 10 Nov 2017 13:00:52 GMT

The below should work if all you need to do is seperate them

=Date(Date#(Left(Date,10), 'YYYY/MM/DD'), 'DD/MM/YYYY')

=Time#(right(Date,8), 'HH:MM:SS')

Re: Date time stamp conversion: YYYY/MMDD HH:MM:SS

sergio0592 — Fri, 10 Nov 2017 13:03:12 GMT

Try with:

Date_filed=Date#(left(Timestamp_field,10),'YYYYMMDD')

Time_field=Time#(right(Timestamp_field ,8),'HH:MM:SS')

After, you can format them with Date and Time function.

Re: Date time stamp conversion: YYYY/MMDD HH:MM:SS

isciberras — Fri, 10 Nov 2017 13:19:09 GMT

Tried everything and took ages, but this worked perfectly thank you so much

Re: Date time stamp conversion: YYYY/MMDD HH:MM:SS

isciberras — Fri, 10 Nov 2017 13:19:33 GMT

Thank you for your help

Re: Date time stamp conversion: YYYY/MMDD HH:MM:SS

isciberras — Fri, 10 Nov 2017 13:19:49 GMT

Thank you for your help

Re: Date time stamp conversion: YYYY/MMDD HH:MM:SS

isciberras — Fri, 10 Nov 2017 13:20:32 GMT

Thank you for your help