cast (-18989899 as date)

UDA
Enthusiast

cast (-18989899 as date)

Hi ,

Table DDL is showing as
Date_column DATE FORMAT 'YYYY-MM-DD' NOT NULL DEFAULT DATE ,

But I am able to insert -18989899 in to the Date_column
And when I do select I am fetching a blank in the column (not null value)

I got little bit confused how sel cast (-18989899 as date) is converted in to date and there is no date value in the output

thanks in advance
Rahul
5 REPLIES
Enthusiast

Re: cast (-18989899 as date)

Hi,
see Teradata SQL Reference: Data type and Literars, p. 101:

Internal Representation of DATE
Internally, Teradata Database stores each DATE value as a four-byte signed integer using the
following formula:
(YEAR - 1900) * 10000 + (MONTH * 100) + DAY
where the YEAR, MONTH, and DAY components, defined appropriately for the Gregorian
calendar,...

so your number is valid date 1st January 1 and you should not recieve blank or null value

brgds

petr
Teradata Employee

Re: cast (-18989899 as date)

As noted, -18989899 is the Teradata INTEGERDATE equivalent of date'0001-01-01'.
When you use Windows local format to display that date (e.g. in SQL Assistant, see Tools/Options/General) then it is displayed as blank.
Enthusiast

Re: cast (-18989899 as date)

Hi Fred and Petr

Thank a lot for the clarifying my doubt
But there is something fishy, from ‘0001-01-01’ to ‘0099-12-31’ its blank
Do you know why this 1st 99yrs is treated as blank in Teradata ???
Teradata Employee

Re: cast (-18989899 as date)

It's not Teradata that is "blanking out" dates in that range, it's Windows.

Use BTEQ, or tell SQL Assistant not to use Windows formatting for dates, or tell Teradata to CAST the value to CHAR before returing it to the client.
Enthusiast

Re: cast (-18989899 as date)

Thank you Fred
I tried in Bteq and as well as in Teradata its working :)
GR8