Efficient Teradata Date Calculations Avoiding INTEGER Values

Teradata internally stores the date as INTEGER.

Calculate dates after January 1st, 1900 using this formula:

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

While this technique is compact and efficient, I recommend avoiding it due to its poor readability and inaccuracy 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

The aforementioned computations utilize the EXTRACT function.

Avoid using substrings for date calculations.

Here are three methods for determining the first day of any month based on a comprehensive test table.

The CPU times utilized vary significantly. While the computations with INTEGER values (second row of the table) are the most efficient, they should be avoided due to their lack of clarity.

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)