topic Re: Time Format and Time Zone in QlikView in QlikView

Time Format and Time Zone in QlikView

manideep78 — Fri, 12 Feb 2021 20:30:04 GMT

Hi everyone,

I have a date format like this

1/1/2001 05:00:00.000000-05:00

8/1/2018 00:00:00.000000-04:00

and wanted to display in table box like this

2001-01-01 05:00:00.000000-05:00

2018-08-01 00:00:00.000000-04:00

Please suggest

Re: Time Format and Time Zone in QlikView

Frank_Hartmann — Sat, 13 Feb 2021 09:58:10 GMT

see attached qvw!

SET TimestampFormat='D/M/YYYY hh:mm:ss.ffffff-hh:mm'; tmp: Load * Inline [ Date 1/1/2001 05:00:00.000000-05:00 8/1/2018 00:00:00.000000-04:00 ]; NoConcatenate Final: Load *, Date(Date,'YYYY-MM-DD hh:mm:ss.ffffff-hh:mm') as Date_New; Load * Resident tmp; drop Table tmp;

hope this helps

Re: Time Format and Time Zone in QlikView

manideep78 — Mon, 15 Feb 2021 21:14:28 GMT

Hi @Frank_Hartmann

Thank you for the response.

This is working while the data is in excel.

But I'm loading the same date format from Teradata and doesn't see m to work.

also, -05:00 or -04:00 is the UTC time zone when day light is on/of.

UTC-05:00 or UTC-04:00

I have dates like this (M/DD/YYYY hh:mm:ss.ffffff-05:00)

and I need to display like this(YYYY-MM-DD hh:mm:ss.ffffff-05:00)

Please suggest.

Re: Time Format and Time Zone in QlikView

Frank_Hartmann — Tue, 16 Feb 2021 08:41:15 GMT

What happens if you load your date/table as it is and then using resident load to transform the date/table shown in my example?

SET TimestampFormat='D/M/YYYY hh:mm:ss.ffffff-hh:mm'; Teradata: Load * from Teradata.... noconcatenate Final: Load *, Date(Date,'YYYY-MM-DD hh:mm:ss.ffffff-hh:mm') as Date_New; Load * Resident Teradata; drop Table Teradata;

Re: Time Format and Time Zone in QlikView

manideep78 — Tue, 16 Feb 2021 15:31:15 GMT

Hi @Frank_Hartmann

Please see the attached image for reference. In the highlighted area, it is reflecting the hours and mins again instead of time zone.

it should have been -05:00 or -04:00

This is how dates are coming out of Teradata.

I just want to display as is except the date format YYYY-MM-DD instead of M/DD/YYYY