Assigning right century to the year incoming as YY format

Database
Enthusiast

Assigning right century to the year incoming as YY format

I have data coming as mm/dd/yy (string) in a file while loading to a teradata table date field, it is displays mm/dd/yyyy. The issue is it is assigning 21st centuary by default.
For ex: 01/01/99 is being stored as 01/01/2099.
How can we make the program to determine right centuary considering the year should not be greater than current year?
1 REPLY
Apprentice

Re: Assigning right century to the year incoming as YY format

Hi Nina,

 

You have a couple of choices.

 

1) Use CenturyBreak setting in DBSControl. This is a system-wide setting and therefore will affect all such processes. you will also need to change the value each year.

2) Do it in your code. Load this field into a DATE column as part of the load process and then use logic to determine if the 'year' needs to be changed.

Something like:

stage#1; load into a T!. The column in question is defined as DATE. The CenturyBreak setting will be used during the load process.

stage#2: move data from T! into T2 including folowing code

(CASE
 WHEN date-column >= DATE THEN ADD_MONTHS(date-column,-100 * 12)
 ELSE date-column
 END) AS new-date-column

HTH

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com