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).
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.