Interview Questions

Date and Time Processing

System Calendar

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:

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:

In BTEQ

  foldline on
      .sidetitles on
      sel * from sys_calendar.calendar
      where calendar_date=1011001 ;

      1 Row Returned

      calendar_date        01/10/01
      day_of_week           2
      day_of_month          1
      day_of_year          274
      day_of_calendar      37164
      weekday_of_month       1
      week_of_month          0
      week_of_year          39
      week_of_calendar     5309
      month_of_quarter       1
      month_of_year         10
      month_of_calendar    1222
      quarter_of_year       3
      quarter_of_calendar   407
      year_of_calendar     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:

SELECT Order_date
  ,Order_total (FORMAT  '$$$$,$$$.99')
  ,Quarter_of_Year
  ,Week_of_Month
FROM Order_table  INNER JOIN Sys_Calendar.Calendar
  ON  Order_date = calendar_date
  WHERE Quarter_of_Year = 3
  AND Week_of_Month < 2;
2 Rows Returned

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:

CT Week_Days
  ( Wkday_no SMALLINT  unique not null
  ,Wkday_Day CHAR(9) ) ;
  ins into Week_Days  (1,'Sunday');
  ins into Week_Days  (2,'Monday');
  ins into Week_Days  (3,'Tuesday');
  ins into Week_Days  (4,'Wednesday');
  ins into Week_Days  (5,'Thursday');
  ins into Week_Days  (6,'Friday');
  ins into Week_Days  (7,'Saturday');

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

SELECT Order_date
  ,Order_total (FORMAT  '$$$$,$$$.99')
  ,Day_of_Week
  ,Wkday_Day
  FROM Order_table  INNER JOIN Sys_Calendar.Calendar
  ON  order_date = calendar_date
  INNER  JOIN Week_Days
  ON  Day_of_Week = Wkday_no
  WHERE Quarter_of_Year  = 3
  AND Week_of_Month <  2
  ;
2 Rows Returned

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).


Pragna Meter
Next Chapter  
e-University Search
Related Jobs