System Calendar - Teradata

Compatibility: Teradata Extension

Also in V2R3, Teradata has a system calendar that is very helpful when date comparisons more complex than month, day and year are needed. For example, most businesses require comparisons from 1st quarter to 2nd quarter. It is best used to avoid maintaining your own calendar table or performing your own sophisticated SQL calculations to derive the needed date perspective.

Teradata's calendar is implemented using a base date table named caldates with a single column named CDATES. The base table is never referenced. Instead, it is referenced using the view named CALENDAR. The base table contains rows with dates January 1, 1900
through December 31, 2100. The system calendar table and views are stored in the Sys_calendar database. This is a calendar from January through December and has nothing to do with fiscal calendars.

The purpose of the system calendar is to provide an easy way to compare dates. For example, comparing activities from the first quarter of this year with the same quarter of last year can be quite valuable. The System Calendar makes these comparisons easy compared to trying to figure out the complexity of the various dates.

The next page contains a list of column names, their respective data types, and a brief explanation of the potential values calculated for each when using the CALENDAR view:

potential values calculated for each when using the CALENDAR view

potential values calculated for each when using the CALENDAR view

potential values calculated for each when using the CALENDAR view

potential values calculated for each when using the CALENDAR view

It appears that the least useful of these columns are all the names that end with "_of_calendar." As seen in the above descriptions, these values are all calculated starting at the calendar reference date of January 1, 1900. Unless a business transaction occurred on that date, they are meaningless.

The biggest benefit of the System Calendar is for determining the following: Day of the Week, Week of the Month, Week of the Year, Month of the Quarter and Quarter of the Year.

Most of the values are very straightforward. However, the column called Week_of_Month deserves some discussion. The description indicates that a partial week is week number 0. A partial week is any first week of a month that does not start on a Sunday. Therefore, not all months have a week 0 because some do start on Sunday.

Having these column references available, there is less need to make as many compound comparisons in SQL. For instance, to simply determine a quarter requires 3 comparisons, one for each month in that quarter. Worse yet, each quarter of the year will have 3 different months. Therefore, the SQL might require modification each time a different quarter was desired.

The next SELECT uses the System Calendar to obtain the various date related rows for October 1, 2001:


Since the calendar is a view, it is used like any other table and columns are selected or compared from it. However, not all columns of all rows are needed for every application. Unlike a user created calendar, it will be faster. The primary reason for this is due to reduced input requirements.

Each date is only 4 bytes stored as DATE. The desired column values are materialized from the stored date. It makes sense that less IO equates to a faster response. So, 4 bytes per date are read instead of 32 or more bytes per date needed. There may be hundreds of different dates in a table with millions of rows. Therefore, utilizing the Teradata system calendar makes good sense.

Since the system calendar is a view or virtual table, its primary access is via a join to a stored date (i.e. billing or payment date). Whether the date is the current date or a stored date, it can be joined to the calendar. When a join is performed, a row is materialized in cache to represent the various aspects of that date.

The following examples demonstrate the use of the WHERE clause for these comparisons using months instead of quarters (WHERE Month_of_Year = 1 OR Month_of_Year = 2 OR Month_of_Year = 3 vs. WHERE Quarter_of_Year = 1) and the Day_of_week column instead of DATE MOD 7 to simplify coding:

2 Rows ReturnedWHERE clause for these comparisons using months instead of quarters

As nice as it is to have a number that represents the day of the week, it still isn't as clear as it might be with a little creativity.

This CREATE TABLE builds a table called Week_Days and populates it with the English name of the week days:

Once the table is available, it can be incorporated into SQL to make the output easier to read and understand, like the following:

2 Rows ReturnedCREATE TABLE builds a table called Week_Days

As demonstrated in this chapter, there are many ways to incorporate dates and date logic into SQL. The format of the date can be adjusted using the DATEFORM. The SQL may use ANSI functions or Teradata capabilities and functions. Now you are ready to go back and forth with a date (pun intended).

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Teradata Topics