Archives of the TeradataForum

Message Posted: Sun, 10 Feb 2013 @ 10:18:11 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: Julian date issue

From:		Dieter Noeth

Anomy.Anom wrote:

in mapping doc we have the following Rule to load the date. This can be derived from the Julian date (JJJ) and year (YY)on the source column: RRYYJJJBBBSSS.

Seems like everybody forgot about the Y2K problem, at least this is save for the next 86 years :-)

     > SEL  SUBSTR(cld.sourcecol ,3,5) AS xyz,
     > xyz (DATE,FORMAT'yyddd') AS bb,
     > cld.sourcecol
     >
     > 12121   1912-04-30   2012129999999
     > 12128   1912-05-07   991212999xxxx

So this shows when I am converting into regular date from 'yymmm' , its assuming the 20th century . Teradata expect us to use 'yyyy' instead of 'yy' fields for 21st century.

There are two solutions:

#1: fix this specific import

          SEL  '20' || SUBSTR(cld.sourcecol ,3,5) AS xyz,
               xyz (DATE,FORMAT'yyddd') AS bb

#2: global change

the dba might change CenturyBreak in dbscontrol's General (it's probably set to the default 0), e.g. 68 would assumes 21th century for any xx < 68. Caution: this might affect other queries/loads

Dieter


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference