Some Useful 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, I would avoid the 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

The 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) use the fewest resources, you should avoid them because of their poor readability.