Dates are not saved in specified format during load
I have this issue with date formatting which I'd like some insight.
I have date fields coming from a QVD and when it is loaded in a specific manner, the formatting I specify is ignored. In the example below, the fields ACTIVATION_DT and TERMINATION_DT is saved in 'DD/MM/YYYY' format in the QVD.
SET TimeFormat='h:mm:ss TT';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
DATE(ACTIVATION_DT, 'YYYY-MM-DD') AS ACTIVATION_DT,
DATE(TERMINATION_DT, 'YYYY-MM-DD') AS TERMINATION_DT
DROP TABLE FACT_TABLE_TEMP;
When viewing the fields, the fields ACTIVATION_DT and TERMINATION_DT is still shown as 'DD/MM/YYYY' and not 'YYYY-MM-DD' as specified in the load script. Renaming the fields to another name works, but I would like to use the original names of those fields. What is the issue about here?
Re: Dates are not saved in specified format during load
But it is still getting read as date? To check this you can check if the date is left or right oriented in the list box object. If it is right oriented, then it is getting lead correctly by QlikView, otherwise its not. Look here for more details on how to read dates in QlikView
Now if it is read properly, you can change its format (for everywhere in the application) by going to the document properties and give it a new format. This format will then be used everywhere in the application, regardless of whatever format you provide in the script (Have a look at the attached application)