0

The current arrangement is:

  • SAP DSO: contains the data columns in the form of dd-mm-yyyy.
  • The BODS job fetches the data from the DSO and loads to the landing table.
  • The corresponding date column in Teradata is dd-mm-yy.

When the dates are loaded to teradata, year 2014 is converted to 1914. There is no transformation involved. Direct mapping between the source and target.

Why does it convert to 1914?

This issue started happening only a few months back. Not sure what to check.

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
user3055262
  • 405
  • 3
  • 9
  • 20

2 Answers2

2

To show the correct year in your date, use the DateTime format dd-mm-rrrr.

When dates are stored in one century but refer to another century, dates can be shown with the wrong prefix. The rrrr year format works as so;

If the specified two-digit year is 00 to 49, then

  • If the last two digits of the current year are 00 to 49, then the returned year has the same first two digits as the current year.

  • If the last two digits of the current year are 50 to 99, then the first 2 digits of the returned year are 1 greater than the first 2 digits of the current year.

If the specified two-digit year is 50 to 99, then

  • If the last two digits of the current year are 00 to 49, then the first 2 digits of the returned year are 1 less than the first 2 digits of the current year.

  • If the last two digits of the current year are 50 to 99, then the returned year has the same first two digits as the current year.

I came across a similar issue with BOXI 3.1 and Oracle.

After creating a number of tables with Date fields with the DateTime format as dd/mm/yyyy, and building a universe with this format in mind, I noticed on testing that some of the date results were displaying incorrectly e.g. 01/07/1993 was displaying as 01/07/2093. This was due to the data being loaded into the table having only 2 yy digits e.g. 01/07/93where as Oracle was expecting a DateTime format with 4 yyyy digits.

In turn, Oracle was forcing the year format into 4 digits but as the year was in the last century (20th) but stored in the 21st Century, the wrong century was prefixed onto the year.

To solve, I used the rrrr DateTime Format for the year. Full explaination from Oracle at this link, and further explaination can be found here.

When I recreated the table with the DateTime format as dd-mm-rrrr, the date displayed correctly.

I hope this helps.

TheOriginal
  • 50
  • 8
  • The answer seems to correspond to the question, but the question is about Teradata. How are Oracle and Teradata related? It's not because both Teradata and Oracle have the same named format `rrrr` that they work identically. Also, it doesn't explain clearly why `dd-mm-rrrr` would be better than `dd-mm-yyyy` (in Teradata of course). – Sandra Rossi Jun 28 '23 at 15:24
0

DBSControl for CenturyBreak specifies which two-digit years are interpreted as 20th century and which are interpreted as 21st century. If your system has this configured as a non-zero value, which is the default, than this is quite possibly the reason you are seeing the behavior with your data.

CenturyBreak does not affect four digit years or dates input as numeric.

If CenturyBreak=10, strings such as '00/01/01' and '09/01/01' are interpreted as 2000 and 2009. A string inserted as '14/01/01' is interpreted as 1914.

Check with your DBA to determine if the CenturyBreak has been set to a non-zero value or explicitly convert your data inputs to numeric date values.

Rob Paller
  • 7,736
  • 29
  • 26