Translation of Numbers to Dates - Teradata Vantage NewSQL Engine - 16.20

Teradata Vantage™ Data Types and Literals

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1143-162K

Although not recommended, you can explicitly convert numbers to dates.

Teradata Database stores each DATE value as a four-byte integer using the following formula:

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

For example, December 31, 1985 would be stored as the integer 851231; July 4, 1776 stored as -1239296; and March 30, 2041 stored as 1410330.

The following table demonstrates how numeric dates are interpreted when inserted into a column. Note the translation of the third date, which was probably intended to be 1990-12-01.

This numeric value … Translates to this date value …
901201 1990-12-01
1001201 2000-12-01
19901201 3890-12-01

Notice that this formula best fits two-digit dates in the 1900s. Because of the difficulty of using this format outside of the 1900s, dates are best specified as ANSI date literals instead.