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

Teradata Vantage™ Data Types and Literals

Teradata Database
Teradata Vantage NewSQL Engine
March 2019
Programming Reference

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.