2 Replies Latest reply: May 10, 2016 10:36 PM by Wong Ooi Hong RSS

    Dates are not saved in specified format during load

    Wong Ooi Hong

      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:

       

      Date Error2.png

       

      Thank you in advance!