All Forums Tools
Koushik Chandra 10 posts Joined 08/12
24 Sep 2012
Date (Y2K) Issue

Hi,
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"
WITH tt(colb)
AS

    (

       SELECT 

                   '23-DEC-12' colb

       FROM sys_calendar.caldates 

       WHERE cdate='1900-01-01'

    )

SELECT 

            colb COLB_INPUT_DT_STR,

            CAST(colb AS DATE FORMAT 'dd-MMM-yy') COOKED_COLB

FROM tt;

 

Regards,

Koushik

Qaisar Kiani 337 posts Joined 11/05
24 Sep 2012

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!

dnoeth 4628 posts Joined 11/04
01 Oct 2012

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

Dieter

You must sign in to leave a comment.