Dates are not saved in specified format during load

[#1]

Hi all,

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 ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='YYYY-MM-DD';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET FirstWeekDay=6;

SET BrokenWeeks=1;

SET ReferenceDay=0;

SET FirstMonthOfYear=1;

SET CollationLocale='en-US';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

FACT_TABLE_TEMP:

LOAD

    ACTIVATION_DT,

    TERMINATION_DT

FROM [lib://data/xxx.qvd]

(qvd);

FACT_TABLE:

NOCONCATENATE

LOAD

    DATE(ACTIVATION_DT, 'YYYY-MM-DD') AS ACTIVATION_DT,

    DATE(TERMINATION_DT, 'YYYY-MM-DD') AS TERMINATION_DT

RESIDENT FACT_TABLE_TEMP;

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?

Result of the load below:

Thank you in advance!

[sunny_talwar]

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

Get the Dates Right‌

Why don’t my dates work?

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)

Script

SET DateFormat='M/D/YYYY';

Table:

LOAD Date(Today(), 'DD/MM/YYYY') as Date

AutoGenerate 1;

[#3]

Hi Sunny T,

I'm using QlikSense, and the dates are on the right side as you have mentioned, but I don't think there is a global formatting setting that can be applied in QlikSense.

Wong