Efficient Teradata Date Calculations Avoiding INTEGER Values
Learn how Teradata stores dates internally as INTEGER values and how to efficiently calculate dates after 1900-01-01 using a simple formula. Get more useful Teradata date calculations in this article.
Teradata internally stores the date as INTEGER.
Calculate dates after January 1st, 1900 using this formula:
((year- 1900) * 10000) + (month * 100) + day
Want more practical data engineering analysis like this?
Join DWHPro Letters and get field-tested notes on Teradata, Snowflake, AI, migrations, performance, and enterprise data work. Early subscribers keep launch access before the paid plan launches.
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)
Get the next issue by email.
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) |
Planning or surviving an enterprise data platform migration?
I write regularly about the performance, cost, architecture, and project mistakes that show up in real Teradata, Snowflake, Databricks, and enterprise data work.
Subscribe before the paid plan launches and keep launch access.
Written by Roland Wenzlofsky, founder of DWHPro and author of Teradata Query Performance Tuning. DWHPro has helped data warehouse practitioners for 15+ years.