Time Format and Time Zone in QlikView

[manideep78]

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

[Frank_Hartmann]

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

[manideep78]

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.

[Frank_Hartmann]

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;

[manideep78]

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