I have installed "Teradata Express 13 - Windows" for learning purpose. Now when I am executing the query below in "Teradata SQL Assistant" I am getting output as "12/23/1912" for the column "COOKED_COLB".
Can any one please let me know what is problem here, why the yer part of the date is showing as "1912"
Teradata stores the dates internally in the INTEGER format consisting of CYYMMDD.
C=0 means 19th Century and C=1 means 20th Century.
Because there is no century mentioned in the data '23-DEC-12', so the default value of 0 (19th century) is assumed and Teradata stores it as 122312.
If you specify the date as '23-DEC-2012' in the inner SELECT statement, TD will store the value 1122312, and when you do the casting again, you will get the correct result!
This is not fully correct.
Teradata stores dates internally as an integer using following formula:
(year-1900) * 10000 + month * 100 + day
If a two-digit is year is treated as 20th or 21th century depends on the setting of CenturyBreak (=General Field 14 in dbscontrol). Any value less than CenturyBreak results in 21th century.
On your system it's set to a value less than 13, probably it's still the default of zero.