Date ranges and business calendars beyond recursion and Teradata SYS_CALENDAR

In a previous article, Roland explained how to use recursion to build a data range without touching a physical table. This time, we explore an alternative approach that avoids recursion at the expense of single-AMP, single-row access. We can use this approach to build both a wide date range as well as a narrow one. We can also go beyond the limits of the Teradata SYS_CALENDAR table. The latter ends on 2100-12-31, which might be closer than we think :)).

With no further ado, here is the template query building atop EXPAND ON clause, a Teradata extension to the ANSI SQL:2011 standard.

SELECT Cast(Begin(thePeriod) AS DATE) AS BUSINESS_DATE
FROM dbc.dbcinfov WHERE infokey = 'VERSION'
EXPAND ON Period(DATE '2101-01-01', DATE '2101-02-01') AS thePeriod BY ANCHOR PERIOD DAY;

The execution plan confirms the efficacy of accessing a single row through a single AMP. Teradata adeptly computes the SQL recursive statement internally.

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.

Get the next issue

Using this template query as a starting point, one can efficiently extract more detailed periods (such as the first day of the month or specific weekdays) by adjusting the EXPAND ON parameters. This allows for better customization to match the needs of various business calendars or date ranges.

https://letters.dwhpro.com/content/files/2026/05/teradata-recursions.html

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.