Teradata internally stores the date as INTEGER.
Calculate dates after January 1st, 1900 using this formula:
((year- 1900) * 10000) + (month * 100) + day
The integer encoding is less readable for date arithmetic, but is not intrinsically inaccurate before 1900. The arithmetic encoding and CAST to INTEGER matched for 1899-12-31 (-8769), 1900-01-01, 2000-02-29 and 2024-03-01 in the boundary fixture.
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
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free bookThe 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 figures below are historical observations without a reproduced workload. They do not establish a current performance ranking; prefer typed date arithmetic for clarity.
| CPU Time | Statement |
| 114,40 | thedate – EXTRACT(DAY FROM thedate ) + 1 |
| 74,10 | thedate / 100 * 100 + 1 (DATE) |
| 166,50 | CAST(SUBSTRING(CAST(thedate AS CHAR(10) FORMAT ‘YYYY-MM-DD’) FROM 1 FOR 8) || ’01’ AS DATE FORMAT ‘YYYY-MM-DD’) |
For output formatting rather than date arithmetic, see Teradata date formats with CAST, TO_CHAR and TO_DATE. Keep the value typed as DATE while calculating and format the final result.
Deciding what to change in your data platform?
Compare optimisation, modernisation and migration against your workload and business requirements.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free book