1813605 - The date loaded into Teradata table gets the century part truncated - Data Services

• The issue only happens when using Bulk Loader.
• If the format of the date column is set to ‘yy/mm/dd’ in table definition, its century part will be truncated in the .dat file. For example, a table definition:
  CREATE SET TABLE SAMPLES.DL_DPR_TASK_DT ,NO FALLBACK ,
       NO BEFORE JOURNAL,
       NO AFTER JOURNAL,
       CHECKSUM = DEFAULT
       (
        TASK_GUID CHAR(32) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'TASK_GUID' NOT NULL,
        PRTY CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'PRIORITY',
        ACTL_STRT_DT DATE FORMAT 'YYYY-MM-DD' TITLE 'ACTUAL_START_DATE',
        ACTL_FINI_DT DATE FORMAT 'YYYY/MM/DD' TITLE 'ACTUAL_FINISH_DATE',
        PLAN_STRT_DT DATE FORMAT 'YY/MM/DD' TITLE 'PLAN_START_DATE',
        PLAN_FINI_DT DATE FORMAT 'YYYY-MM-DD' TITLE 'PLAN_FINISH_DATE',
        CMPL SMALLINT TITLE 'COMPLETION',
        MLSTN CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'MILESTONE',
        CNSTRNT_TYP CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CONSTRAINT_TYPE',
        CNSTRNT_DT DATE FORMAT 'YYYY-MM-DD' TITLE 'CONSTRAINT_DATE',
        CONSTR_TYP_FIN CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CONSTR_TYPE_FIN',
        CONSTR_FINDATE DATE FORMAT 'YYYY-MM-DD' TITLE 'CONSTR_FINDATE',
        PLANGROUP CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'PLANGROUP',
        LATST_STRT_DT DATE FORMAT 'YYYY-MM-DD' TITLE 'LATEST_START_DATE',
        LATST_FINI_DT DATE FORMAT 'YYYY-MM-DD' TITLE 'LATEST_FINISH_DATE',
        TOT_FLOAT DECIMAL(9,2) TITLE 'TOTAL_FLOAT',
        DURTN DECIMAL(9,2) TITLE 'DURATION',
        DURTN_UNT CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'DURATION_UNIT',
        CAL_ID CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CALENDAR_ID',
        WRK_EFFRT DECIMAL(9,2) TITLE 'WORK_EFFORT',
        WRK_UNT CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'WORK_UNIT',
        ACT_WRK_EFFRT DECIMAL(18,3) TITLE 'ACT_WORK_EFFORT',
        ACT_WRK_UNT CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'ACT_WORK_UNIT',
        FCST_STRT DATE FORMAT 'YYYY-MM-DD' TITLE 'FORECASTED_STRT',
        FCST_FIN DATE FORMAT 'YYYY-MM-DD' TITLE 'FORECASTED_FIN')
  UNIQUE PRIMARY INDEX XPKDPR_TASK_DATE ( TASK_GUID );
• Once the .dat file is loaded into Teradata with only the 2 digit year, Teradata is using the y2k logic to add a century (which in this case happens to be the incorrect century of 1900).
  

• Teradata
• Data Services 4.0

ds,data services,teradata,date,century,1900,bulk load , KBA , EIM-DS , Data Services , Problem