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

Employing the aforementioned technique is resourceful and demands minimal input. Nevertheless, I would refrain from utilizing it due to its intricate legibility.

However, I suggest avoiding the aforementioned calculation due to its complexity 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.