Date (Y2K) Issue

Tools
Enthusiast

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

2 REPLIES
Enthusiast

Re: Date (Y2K) Issue

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!

Junior Contributor

Re: Date (Y2K) Issue

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