Some useful Teradata Date Calculations

Teradata Date Calculations

Teradata saves the date internally as INTEGER.

Dates after 01.01.1900 can be calculated with the following formula:

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

Calculation using the above method is efficient and requires only a few resources. However, I would avoid it because it is difficult to read.

Teradata stores date internally as INTEGER values. Dates after 1900-01-01 can be calculated with the following formula:
((year- 1900) * 10000) + (month * 100) + day

Nevertheless, personally, i would avoid above calculation as it is difficult to read and wrong for dates before 1900.

Here are some more useful Teradata Date Calculations:

First day of the month:
CURRENT_DATE – (EXTRACT(DAY FROM CURRENT_DATE) – 1)

Last day of the previous month:
CURRENT_DATE – EXTRACT(DAY FROM CURRENT_DATE)

Last day of the month, n months ago:
ADD_MONTHS(CURRENT_DATE – (EXTRACT(DAY FROM CURRENT_DATE) – 1), -n + 1) – 1

First day of the month, n months ago:
ADD_MONTHS(CURRENT_DATE – (EXTRACT(DAY FROM CURRENT_DATE) – 1), -n)

First day of the year:
ADD_MONTHS(CURRENT_DATE – (EXTRACT(DAY FROM CURRENT_DATE) – 1), -EXTRACT(MONTH FROM CURRENT_DATE) + 1)

Day of week (1-7; 1 = monday, 2 = tuesday, …)
(CURRENT_DATE – DATE ‘0001-01-01’) MOD 7 + 1

All the above calculations use the EXTRACT function.

In any case, you should avoid substrings for date calculations.

Below are three alternative ways to calculate the first day of a month for any date (based on a large test table).

As you can see, the CPU times used are very different. Although the calculations with INTEGER values (the second table row) uses the fewest resources, you should avoid them because of their poor readability.

CPU Time Statement
114,40 thedate – EXTRACT(DAY FROM thedate ) + 1
74,10 thedate  / 100 * 100 + 1 (DATE)
166,50 thedate  – SUBSTRING(thedate  FROM 1 FOR 8) || ’01’ (DATE)

 

Roland Wenzlofsky
 

Roland Wenzlofsky is a graduated computer scientist and Data Warehouse professional working with the Teradata database system for more than 15 years. He is experienced in the fields of banking and telecommunication with a strong focus on performance optimization.

>