17.10 - Translation of Numbers to Dates - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Data Types and Literals

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
English (United States)

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

SQL Engine 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.