Teradata Number Date Alternative in Redshift

Post author:Vithal S
Post last modified:January 26, 2022
Post category:Redshift
Reading time:5 mins read

The relational database such as Teradata supports numeric dates. For example, 851231 is a valid representation for date ‘1985-12-31’. These types of Teradata integer date formats are not yet supported in Amazon Redshift. In this article, we will check what is Teradata number date alternative in AWS Redshift.

Teradata Number Date Values

There are many Teradata features that are not yet available in any other Relational or databases. One of such features is Teradata number date values. Teradata Database stores each DATE value as a four-byte integer using the following formula:

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

For example, 1985-01-01 date would be stored as the integer 800101; July 4, 1776 stored as -1239296; and March 30, 2041 stored as 1410330 and so on.

Translation of Numbers to Dates in Teradata

You can simply use the CAST conversion function to convert your integer date to proper date format.

For example, following example casts the numeric integer expression to a date format.

select CAST(800101 as date) dt;
      dt
--------
1980-01-01

Teradata Number Date Alternative in Redshift

You cannot use the Redshift type conversion function to convert Teradata integer date values to a proper value. There are no specific date functions in Redshift that allows you to convert number to a proper date format.

For example, following cast example does not work on Amazon Redshift and end up with “ERROR: cannot cast type integer to date” error.

select CAST(11010101  as date) dt;

However, you can use the following formula to convert integer to date conversion in Redshift.

(INTEGER + 19000000)

You may have to convert given integer value to a text and the apply standard Redshift type conversion functions.

For example, following example converts the numeric integer expression to a date format.

select ((CAST(800101 AS INTEGER) + 19000000)::text)::date as date;
 date
--------
 1980-01-01
(1 row)

Apart from above method, you can also write your own user defined function in Python or SQL to convert numeric to a date format.

For example, following Redshift user defined function converts numbers to a date.

create function numeric_date (int)
  returns date
stable
as $$
  select ((CAST($1 AS INTEGER) + 19000000)::text)::date
$$ language sql;

You can simply use above UDF in your SQL queries.

For example,

select numeric_date(800101) as date;
 date
--------
 1980-01-01
(1 row)