All Forums UDA
04 Feb 2009
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

Petr Horcicka 29 posts Joined 12/08
05 Feb 2009

Hi,see Teradata SQL Reference: Data type and Literars, p. 101:Internal Representation of DATEInternally, Teradata Database stores each DATE value as a four-byte signed integer using thefollowing formula:(YEAR - 1900) * 10000 + (MONTH * 100) + DAYwhere the YEAR, MONTH, and DAY components, defined appropriately for the Gregoriancalendar,...so your number is valid date 1st January 1 and you should not recieve blank or null valuebrgdspetr

Fred 1096 posts Joined 08/04
05 Feb 2009

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.

09 Feb 2009

Hi Fred and PetrThank 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 ???

Fred 1096 posts Joined 08/04
09 Feb 2009

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.

09 Feb 2009

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

You must sign in to leave a comment.