SAP Knowledge Base Article - Preview

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

Symptom

  • 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).
    bulk_loader.png


Read more...

Environment

  • Teradata
  • Data Services 4.0

Product

SAP Data Services 4.0 ; SAP Data Services XI 3.2

Keywords

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

About this page

This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP for Me (Login required).

Search for additional results

Visit SAP Support Portal's SAP Notes and KBA Search.