DWHPro Insights

Efficient Teradata Date Calculations Avoiding INTEGER Values

By Roland Wenzlofsky ·

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

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

Teradata Query Performance Tuning: The DWHPro Guide book cover

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

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 figures below are historical observations without a reproduced workload. They do not establish a current performance ranking; prefer typed date arithmetic for clarity.

CPU TimeStatement
114,40thedate – EXTRACT(DAY FROM thedate ) + 1
74,10thedate  / 100 * 100 + 1 (DATE)
166,50CAST(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.

Explore independent platform assessments

Teradata Query Performance Tuning: The DWHPro Guide book cover

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

Follow DWHPro in Google to see our articles more often in Search.

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

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

Protected by Security by CleanTalk and CleanTalk Anti-Spam