0

CreateDate and RemoveDate are two fields in many of the tables related to TDWM e.g. in WlcDefs. The values and their meanings (according to me) are like:

1100301 - 2010/03/01 1091114 - 2009/11/14

What does the first '1' represent in these date fields? and is my interpretation correct?

flag

1 Answer

2

Teradata dates are stored as integers encoded as:

     (year-1900) * 10000
   +  month      *   100
   +  day

This makes them look pretty reasonable for dates before the year 2000, for example

  • 690720 represents 1960-07-20
  • 840523 represents 1984-05-23
  • 991231 represents 1999-12-31

For dates after 2000, (year-1900) is at least 100, so the date integer is more than 1 million, thus that pesky 1 at the beginning:

  • 1010911 represents 2001-09-11
  • 1091114 represents 2009-11-14
  • 1100301 represents 2010-03-01
link|flag
Carlos, thanks for the quick and perfect response. Is this your observation or is there a Teradata resource out there that states this fact? – Monis Iqbal Mar 2 at 6:18
1 
 You can find this information in the manuals. The reason for this is because Teradata stores DATE fields internally as an INTEGER (as Carlos indicated). You can take this value and CAST() it as a DATE instead of doing the math. – Rob Paller♦ Mar 2 at 13:54

Your Answer

Get an OpenID
or

Not the answer you're looking for? Browse other questions tagged or ask your own question.