Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
manideep78
Partner
Partner

Time Format and Time Zone in QlikView

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

 

4 Replies
Frank_Hartmann
Master II
Master II

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
Partner
Partner
Author

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
Master II
Master II

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
Partner
Partner
Author

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

 

Capture.PNG

 

This is how dates are coming out of Teradata.

Untitled.png

 

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