This course contains the basics of Teradata

Course introduction
Test Your Caliber
Interview Questions
Pragnya Meter Exam


Date and Time Processing

Teradata has a date function and a time function built into the database and the ability to request this data from the system. In the early releases, DATE was a valid data type for storing the combination of year, month and day, but TIME was not. Now, TIME and TIMESTAMP are both valid data types that can be defined and stored within a table.

The Teradata RDBMS stores the date in YYYMMDD format on disk. The YYY is an offset value from the base year of 1900. The MM is the month value from 1 to 12 and the DD is the day of the month. Using this format, the database can currently work with dates beyond the year 3000. So, it appears that Teradata is Y3K compliant. Teradata always stores a date as a numeric INTEGER value.

The following calculation demonstrates how Teradata converts a date to the YYYMMDD date format, for storage of January 1, 1999

The stored data for the date January 1, 1999 is converted to:

Although years prior to 2000 look fairly "normal" with an implied year for the 20th Century, after 2000 years do not look like the normal concept of a year (100). Fortunately, Teradata automatically does all the conversion and makes it transparent to the user. The remainder of this will provide SQL examples using both a numeric date as well as the character formats of ‘YY/MM/DD’ and ‘YYYY-MM-DD’.

The next conversion shows the data stored for January 1, 2000 (notice that YYY=100 or 100 years from 1900):

Additionally, since the date is stored as an integer and an integer is a signed value, dates prior to the base year of 1900 can also be stored. The same formula applies for the date conversion regardless of which century. However, since dates prior to 1900, like 1800 are smaller values, the result of the subtraction is a negative number.

ANSI Standard DATE Reference

CURRENT_DATE is the ANSI Standard name for the date function. All references to the original DATE function continues to work and return the same date information. Furthermore, they both display the date in the same format.


INTEGERDATE is the default display format for most Teradata database client utilities. It is in the form of YY/MM/DD. It has nothing to do with the way the data is stored on disk, only the format of the output display. The current exception to this is Queryman. Since it uses the ODBC, it displays only the ANSI date, as seen below.

Later in this book, the Teradata FORMAT function is also addressed to demonstrate alternative arrangements regarding year, month and day for output presentation.

To change the output default display, see the DATEFORM options in the next section of this.


Teradata was updated in release V2R3 to include the ANSI date display and reserved name. The ANSI format is: YYYY-MM-DD.

Since we are now beyond the year 1999, it is advisable to use this ANSI format to guarantee that everyone knows the difference between all the years of each century as: 2000, 1900 and 1800. If you regularly use tools via the ODBC, which is software for Open Data Base Connectivity, this is the default display format for the date.


Teradata has traditionally been Y2K compliant. In reality, it is compliant to the years beyond 3000. However, the default display format using YY/MM/DD is not ANSI compliant.

In Teradata, release V2R3 allows a choice of whether to display the date in the original display format (YY/MM/DD) or the newer ANSI format (YYYY-MM-DD). When installed, Teradata defaults at the system level to the original format, called INTEGERDATE. However, this system default DATEFORM may be over-ridden by updating the DBS Control record.


  • Controls default display of selected dates
  • Controls expected format for import and export of dates as character strings (‘YY/MM/DD’ or ‘YYYY-MM-DD’) in the load utilities
  • Can be over-ridden by USER or within a Session at any time.

System Level Definition


User Level Definition

CREATE USER username .......


Session Level Declaration

In addition to setting the system default in the control record, a user can request the format for their individual session. The syntax is:


In the above settings, the "|" is used to represent an OR condition. The setting can be ANSIDATE or INTEGERDATE. Regardless of the DATEFORM being used, ANSIDATE or INTEGERDATE, these define load and display characteristics only. Remember, the date is always stored on disk in the YYYMMDD format, but the DATEFORM allows you to select the format for display.

DATE Processing

Much of the time spent processing dates is dedicated to storage and reference. Yet, there are times that one date yields or derives a second date. For instance, once a bill has been sent to a customer, the expectation is that payment comes 60 days later. The challenge becomes the correct calculation of the exact due date.

Since Teradata stores the date as an INTEGER, it allows simple and complex mathematics to calculate new dates from dates. The next SELECT operation uses the Teradata date arithmetic and DATEFORM=INTEGERDATE to show the month and day of the payment due date in 60 days:

SELECT Order_date+60 (Title 'Due Date')  ,Order_date  ,Order_total  (FORMAT '$$$$,$$$.99')FROM  Order_tableWHERE  Order_date > '98/12/31' ;
4 Rows Returned

Besides a due date, the SQL can also calculate a discount period date 10 days prior to the payment due date using the alias name:

SELECT Order_date  ,Order_date +60 AS  Due_Date  ,Order_total (FORMAT  '$$$$,$$$.99')  ,Due_date  -10 (Title 'Discount Date')  ,Order_total*.98  (FORMAT '$$$$,$$$.99', Title 'Discounted')FROM Order_tableWHERE Order_date >  981231 ;
4 Rows Returned

In the above example, it was demonstrated that a DATE + or − an INTEGER results in a new date (date { + | − } integer = date). However, it probably does not make a lot of sense to multiply or divide a date by a number.

As seen earlier in this chapter, the stored format of the date is YYYMMDD. Since DD is the lowest component, the 60 being added to the order date in the above SELECT is assumed to be days. The system is smart enough to know that it is dealing with a date. Therefore, it is smart enough to know that a normal year contains 365 days.
The associative properties of math tell us that equations can be rearranged and still be valid. Therefore, a DATE – a DATE results in an INTEGER (date +|− date = integer). This INTEGER represents the number of days between the dates.

This SELECT uses this principal to display the number of days I was alive on my last birthday:

sel (1001001(date)) - (521001(date)) (Title 'Mike"s Age In Days');
1 Row Returned

Mike's Age in Days


The above example subtracted one of my birthdays (October 1, 2000) with my actual birthday in 1952. Notice how awful an age looks in days! More importantly, notice how I slipped it into the Title the fact that you can use two single quotes to store or display a literal single quote in a character string.

As mentioned above, an age in days looks awful and that is probably why we do not use that format. I am not ready to tell someone I am just a little over 17000. Instead, we think about ages in years. To convert the days to years, again math can be used as seen in the following SELECT:

sel  ((1001001(date)) - (521001(date)))/365 (Title 'Mike"s Age In Years');
1 Row Returned

Mike's Age in Years


Wow! I feel so much younger now. This is where division begins to make sense, but remember, the INTEGER is not a DATE. At the same time, it assumes that all years have 365 days. It only does the math operations specified in the SQL statement.

Now, what day was he born? The next SELECT uses the concatenation, date arithmetic and a blank TITLE to produce the desired output:

sel 'Mike  was born on day ' || ((521001(date)) - (101(date))) MOD 7 (TITLE ");  1 Row  Returned
Mike was born on day 2

The above subtraction results in the number of days between the two dates. Then, the MOD 7 divides by 7 to get rid of the number of weeks and results in the remainder. A MOD 7 can only result in values 0 thru 6 (always 1 less than the MOD operator). Since January 1, 1900 ( 101(date) ) is a Monday, Mike was born on a Wednesday.

The following SELECT uses a year's worth of days to derive a new date that is 365 days away:

SELECT Order_date  ,Order_date  +365 (Title 'Year Later Date')  ,Order_total  (FORMAT '$$$$,$$$.99')  FROM  Order_table  ORDER BY 1  ;
5 Rows Returned

In the above, the year 1999 was not a leap year. Therefore, the value of 365 is used. Likewise, had the beginning year been 2000, then 366 needs to be used because it is a Leap Year. Remember, the system is simply doing the math that is indicated in the SQL statement. If a year were always needed, regardless of the number of days, see the ADD_MONTHS function.


Compatibility: Teradata Extension

The Teradata ADD_MONTHS function can be used to calculate a new date. This date may be in the future (addition) or in the past (subtraction). The calendar intelligence is built-in for the number of days in a month as well as leap year processing. Since the ANSI CURRENT_DATE and CURRENT_TIME are compatible with the original DATE and TIME functions, the ADD_MONTHS works with them as well.

Below is the syntax for the ADD_MONTHS function:

SELECT ADD_MONTHS(<date-column>,  <number-of-months>)  FROM  <table-name>  ;

The next SELECT uses literals instead of table rows to demonstrate the calendar logic used by the ADD_MONTHS function when beginning with the last day of a month and arriving at the last day of February:

SELECT ADD_MONTHS('2000-10-30', 4) AS  FEB_Non_Leap  ,(1001030(date))  + 120 AS Oct_P120  ,ADD_MONTHS('2000-10-30', -8) AS  FEB_Leap_Yr  ,(1001030(date))  - 240 AS Oct_M240  ,ADD_MONTHS('2000-10-30', 12*4) AS  FEB_Leap_Yr2  ,(1001030(date))  + 365*4+1 AS Oct_4Yrs ;
1 Row Returned

Notice, when using the ADD_MONTHS function, that all the output displays in ANSI date form. This is true when using BTEQ or Queryman. Conversely, the date arithmetic uses the default date format. Likewise, the second ADD_MONTHS uses –8, which equates to subtraction or going back in time versus ahead. Additionally, because months have a varying number of days, the output from math is likely to be different than the ADD_MONTHS.

The next SELECT uses the ADD_MONTHS function as an alternative to the previous SELECT operations for showing the month and day of the payment due date in 2 months:

SELECT  ADD_MONTHS(Order_date, 2) (Title 'Due Date')  ,Order_date (FORMAT  'YYYY-MM-DD')  ,Order_total (FORMAT  '$$$$,$$$.99')  FROM Order_table  ORDER BY 2 ;
5 Rows Returned

The ADD_MONTHS function also takes into account the last day of each month. The following goes from the last day of one month to the last day of another month:

SELECT ADD_MONTHS('1998-02-28',12*2) AS Leap_Ahead_2yrs  , ADD_MONTHS('2000-03-31',-1)  AS Leap_Back_2yrs  ,  ADD_MONTHS('2001-06-30',1) AS With30_31 ;
1 Row Returned

Whether going forward or backward or backward in time, a leap year is still recognized using ADD_MONTHS.


Teradata has also been updated in V2R3 to include the ANSI time display, reserved name and the new TIME data type. Additionally, the clock is now intelligent and can carry seconds over into minutes.

CURRENT_TIME is the ANSI name of the time function. All current SQL references to the original Teradata TIME function continue to work.

Although the time could be displayed prior to release V2R3, when stored, it was converted to a character column type. Now, TIME is also a valid data type, may be defined in a table, and retains the HH:MM:SS properties.

As well as creating a TIME data type, intelligence has been added to the clock software. It can increment or decrement TIME with the result increasing to the next minute or decreasing from the previous minute based on the addition or subtraction of seconds.

TIME representation character display length:

TIME (0) - 10:14:38 CHAR(8)
TIME (6) - 10:14:38.201163 CHAR(15)


Compatibility: ANSI

Both DATE and TIME data are special in terms of relational design. Since each is comprised of 3 parts and they are decomposable. Decomposable data is data that is not at its most granular level. For example, you may only want to see the hour.

The EXTRACT function is designed to do the decomposition on these data types. It works with both the DATE and TIME functions. This includes the original and newer ANSI expressions. The operation is to pull a specific portion of the SQL techniques.

The syntax for EXTRACT:

SELECT EXTRACT(YEAR FROM <date-data>)  ,EXTRACT(MONTH FROM <date-data>)  ,EXTRACT(DAY FROM <date-data>)  ,EXTRACT(HOUR FROM <time-data>)  ,EXTRACT(MINUTE FROM <time-data>)  ,EXTRACT(SECOND FROM <time-data>)FROM  <table-name>  ;

The next SELECT uses the EXTRACT with date and time literals to demonstrate the coding technique and the resulting output:

SELECT  EXTRACT(YEAR FROM '2000-10-01') AS Yr_Part  ,EXTRACT(MONTH  FROM '2000-10-01') AS Mth_Part  ,EXTRACT(DAY  FROM '2000-10-01') AS Day_Part  ,EXTRACT(HOUR  FROM '10:01:30') AS Hr_Part  ,EXTRACT(MINUTE  FROM '10:01:30') AS Min_Part  ,EXTRACT(SECOND  FROM '10:01:30') AS Sec_Part ;
1 Row Returned

The EXTRACT can be very helpful when there is a need to have a single component for controlling access to data or the presentation of data. For instance, when calculating aggregates, it might be necessary to group the output on a change in the month. Since the data represents daily activity, the month portion needs to be evaluated separately.

The Order table below is used to demonstrate the EXTRACT function in a SELECT:

The following SELECT uses the EXTRACT to only display the month and also to control the number of aggregates displayed in the GROUP BY:

SELECT EXTRACT(Month  FROM Order_date)  ,COUNT(*) AS  Nbr_of_rows  ,AVG(Order_total)FROM Order_table  GROUP BY 1  ORDER BY 1 ;
4 Rows Returned

The next SELECT operation uses entirely ANSI compliant code with DATEFORM=ANSIDATE to show the month and day of the payment due date in 2 months and 4 days, notice it uses double quotes to allow reserved words as alias names and ANSIDATE in the comparison and display:

SELECT 'Due Date:'  (Title ") /* title as 2 single quotes for no title */  ,EXTRACT(Month  FROM Order_date+64) AS "Month"  ,EXTRACT(Day  FROM Order_date+64) AS "Day"  ,EXTRACT(Year  FROM Order_date+64) AS "Year"  ,Order_date (FORMAT  'mmmbdd,byyyy')  ,Order_totalFROM Order_tableWHERE Order_date >  '1998-12-31'  ORDER BY 2,3 ;
4 Rows Returned

Implied Extract of Day, Month and Year

Compatibility: Teradata Extension

Although the EXTRACT works great and it is ANSI compliant, it is a function. Therefore, it must be executed and the parameters passed to it to identify the desired portion as data. Then, it must pass back the answer. As a result, there is additional overhead processing required to use it.

It was mentioned earlier that Teradata stores a date as an integer and therefore allows math operations to be performed on a date. The syntax for implied extract:

SELECT <date-data> MOD 100 /* extracts the day */,(<date-data> /100) MOD 100 /* extracts the month */,<date-data> /10000 +1900 /* extracts the year */  FROM  <table-name>  ;

The following SELECT uses math to extract the three portions of Mike's literal birthday:

SELECT ((1011001(date)) MOD 100 ) AS Day_portion, (((1011001(date))/100) MOD 100 ) AS Month_portion, ((1011001(date))/10000 + 1900) AS Year_portion ;
1 Row Returned

Remember that the date is stored as yyymmdd. The literal values are used here to provide a date of Oct. 1, 2001. The day portion is obtained here by making the dd portion (last 2 digits) the remainder from the MOD 100. The month portion is obtained by dividing by 100 to eliminate the dd to leave the mm (new last 2 digits) portion the remainder of the MOD 100. The year portion is the trickiest. Since it is stored as yyy (yyyy – 1900), we must add 1900 to the stored value to convert it back to the yyyy format. What do you suppose the EXTRACT function does? Same thing.


Another new data type, added to Teradata in V2R3 to comply with the ANSI standard, is the TIMESTAMP. TIMESTAMP is now a display format, a reserved name and a new data type. It is a combination of the DATE and TIME data types combined together into a single column data type.

Timestamp representation character display length:

TIMESTAMP(0) 1998-12-07 11:37:58 CHAR(19)
TIMESTAMP(6) 1998-12-07 11:37:58.213000 CHAR(26)

Notice that there is a space between the DATE and TIME portions of a timestamp. This is a required element to delimit or separate the day from the hour.


In V2R3, Teradata has the ability to access and store both the hours and the minutes reflecting the difference between the user's time zone and the system time zone. From a World perspective, this difference is normally the number of hours between a specific location on Earth and the United Kingdom location that was historically called Greenwich Mean Time (GMT). Since the Greenwich observatory has been "decommissioned," the new reference to this same time zone is called Universal Time Coordinate (UTC).

A time zone relative to London (UTC) might be:

A time zone relative to New York (EST) might be:

Here, the time zones used are represented from the perspective of the system at EST. In the above, it appears to be backward. This is because the time zone is set using the number of hours that the system is from the user.

To show an example of TIME values, we randomly chose a time just after 10:00AM. Below, the various TIME with time zone values are designated as:

TIME  '10:17:38'                       - TIME(0)  TIME '10:17:38-08:00'                - TIME(0) WITH TIME ZONE  TIME  '10:17:38.213000+09:30'         - TIME WITH TIME ZONE

TIMESTAMP with time zone is represented as:

TIMESTAMP  '1999-10-01 10:17:58' - TIMESTAMP(0)  TIMESTAMP  '1999-10-01 10:17:58-08:00'                     -  TIMESTAMP(0) WITH TIME ZONE  TIMESTAMP  '1999-10-01 10:17:58.213000+09:30'                     -  TIMESTAMP WITH TIME ZONE

The default, for both TIME and TIMESTAMP, is to display six digits of decimal precision in the second's portion. Time zones are set either at the system level (DBS Control), the user level (when user is created or modified), or at the session level as an override.


A Time Zone should be established for the system and every user in each different time zone. Setting the system default time zone:

MODIFY  GENERAL 16 = x /* Hours, n= -12 to 13 */MODIFY  GENERAL 17 = x /* Minutes, n = -59 to 59 */

Setting a User's time zone requires choosing either LOCAL, NULL, or a variety of explicit values:

 CREATE  USER mjl  TIME ZONE  = LOCAL /* use system level */             = NULL /*  no default, set to system or session level at logon               */             = '16:00'  /* explicit setting */             = -'06:30'  /* explicit setting */

Setting a Session's time zone:

SET TIME  ZONE LOCAL ; /* use system level */  SET TIME  ZONE USER ; /* use user level */  SET TIME  ZONE INTERVAL '08:00' HOUR TO MINUTE ; /* explicit setting */

A Teradata session can modify the time zone during normal operations without requiring a logoff and logon.


A user's time zone is now part of the information maintained by Teradata. The settings can be seen in the extended information available in the HELP SESSION request.

/* In BTEQ  - adjust output format */  .foldline  on  .sidetitles  HELP  SESSION;  1 Row  Returned
User Name MJL Account Name MJL Logon Date 00/10/15 Logon Time 08:43:45 Current DataBase Accounting Collation ASCII Character Set ASCII Transaction Semantics Teradata Current DateForm IntegerDateSessionTime Zone00:00 Default Character Type LATIN Export Latin 1 Export Unicode 1
Export Unicode Adjust 0
Export KanjiSJIS 1
Export Graphic 0

By creating a table and requesting the WITH TIME ZONE option for a TIME or TIMESTAMP data type, this additional offset is also stored.

The following SHOW command displays a table containing one timestamp column with TIME ZONE and one column as a timestamp column without TIME ZONE:

SHOW TABLE  Tstamp_test;  Text of  DDL Statement Returned  CREATE SET  TABLE MIKEL.Tstamp_test ,NO FALLBACK ,  NO BEFORE  JOURNAL,  NO AFTER  JOURNAL  ( TS_zone  CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC,TS_with_zone TIMESTAMP(6) WITH TIME ZONE,  TS_without_zone  TIMESTAMP(6))  UNIQUE  PRIMARY INDEX ( TS_zone );  INSERT  INTO Tstamp_test ('EST', timestamp '2000-10-01 08:12:00',  timestamp  '2000-10-01 08:12:00');  SET TIME  ZONE INTERVAL '05:00' HOUR TO MINUTE ;  INSERT  INTO Tstamp_test ('UTC', timestamp '2000-10-01 08:12:00',  timestamp  '2000-10-01 08:12:00');  SET TIME  ZONE INTERVAL -'03:00' HOUR TO MINUTE ;  INSERT  INTO Tstamp_test ('PST', timestamp '2000-10-01 08:12:00',  timestamp  '2000-10-01 08:12:00');  SET TIME  ZONE INTERVAL -'11:00' HOUR TO MINUTE ;  INSERT  INTO Tstamp_test ('HKT', timestamp '2000-10-01 08:12:00',  timestamp  '2000-10-01 08:12:00');

As rows were inserted into the table, the time zone of the user's session was automatically captured along with the data for TS_with_zone. Storing the time zone requires an additional 2 bytes of storage beyond the date+time requirements.

The next SELECT show the data rows currently in the table:

SELECT *  FROM Tstamp_test ;
4 Rows Returned

Normalizing TIME ZONES

Teradata has the ability to incorporate the use of time zones into SQL for a relative view of the data based on one locality versus another.

This SELECT adjusts the data rows based on their TIME ZONE data in the table:

SELECT  Ts_zone  ,TS_with_zone,CAST(TS_with_zone AS TIMESTAMP(6)) AS T_NormalFROM  Tstamp_test  ORDER BY 3  ;
4 Rows Returned

Notice that the Time Zone value was added to or subtracted from the time portion of the time stamp to adjust them to a perspective of the same time zone. As a result, at that moment, it has normalized the different Times Zones in respect to the system time.

As an illustration, when the transaction occurred at 8:12 AM locally in the PST Time Zone, it was already 11:12 AM in EST, the location of the system. The times in the columns have been normalized in respect to the time zone of the system.

DATE and TIME Intervals

To make Teradata SQL more ANSI compliant and compatible with other RDBMS SQL, NCR has added INTERVAL processing. Intervals are used to perform DATE, TIME and TIMESTAMP arithmetic and conversion.

Although Teradata allowed arithmetic on DATE and TIME, it was not performed in accordance to ANSI standards and therefore, an extension instead of a standard. With INTERVAL being a standard instead of an extension, more SQL can be ported directly from an ANSI compliant database to Teradata without conversion.

Additionally, when a data value was used to perform date or time math, it was always "assumed" to be at the lowest level for the definition (days for DATE and seconds for TIME). Now, any portion of either can be expressed and used.

Using Intervals

To use the ANSI syntax for intervals, the SQL statement must be very specific as to what the data values mean and the format in which they are coded. ANSI standards tend to be lengthier to write and more restrictive as to what is and what is not allowed regarding the values and their use.

Simple INTERVAL Examples using literals:


Complex INTERVAL Examples using literals:


For several of the INTERVAL literals, their use seems obvious based on the literal nonnumeric literals used. However, notice that the HOUR TO MINUTE and the MINUTE TO SECOND above, are not so obvious. Therefore, the declaration of the meaning is important. For instance, notice that they are coded as character literals. This allows for use of a slash (/), colon (: ) and space as part of the literal. Also, notice the use of a negative time frame requires a "−" sign to be outside of the quotes. The presence of the quotes also denotes that the numeric values are treated as character for conversion to a point in time.

The format of a timestamp requires the space between the day and hour when using intervals. For example, notice the blank space between the day and hour in the compound DAY TO HOUR interval. Without the space, it is an error.

INTERVAL Arithmetic with DATE and TIME

To use DATE and TIME arithmetic, it is important to keep in mind the results of various operations.

Note: It makes little sense to add two dates together.

Traditionally, the output of the subtraction is an integer, up to 2.147 billion. However, Teradata knows that when an integer is used in a formula with a date, it must represent a number of days. The following uses the ANSI representation for a DATE:

SELECT  (DATE '1999-10-01' - DATE '1988-10-01') AS Assumed_Days ;
1 Row Returned



The next SELECT uses the ANSI explicit DAY interval:

SELECT (DATE '1999-10-01' - DATE '1988-10-01') DAY AS Actual_Days ;  ****  Failure 7453 Internal Field Overflow

The above request fails on an overflow of the INTERVAL. Using this ANSI interval, the output of the subtraction is an interval with 4 digits. The default for all intervals is 2 digits and therefore the overflow occurs until the SELECT is modified with DAY(4), below:

SELECT  (DATE '1999-10-01' - DATE '1988-10-01') DAY(4) AS Actual_Days ;  1 Row  Returned



Normally, a date minus a date yields the number of days between them. To see months instead, the following SELECT operations use literals to demonstrate the conversions performed on various DATE and INTERVAL data:

SELECT  (DATE '2000-10-01' – DATE '1999-10-01') MONTH (Title 'Months') ;  1 Row  Returned



The next SELECT shows INTERVAL operations used with TIME:

SELECT  (TIME '12:45:01' - TIME '10:10:01') HOUR AS Actual_hours  ,(TIME  '12:45:01' - TIME '10:10:01') MINUTE(3) AS Actual_minutes  ,(TIME  '12:45:01' - TIME '10:10:01') SECOND(4) AS Actual_seconds  ,(TIME  '12:45:01' - TIME '10:10:01') SECOND(4,4) AS Actual_seconds4 ;
1 Row Returned

Although Intervals tend to be more accurate, they are more restrictive and therefore, more care is required when coding them into the SQL constructs. However, one miscalculation, like in the overflow example, and the SQL fails. Additionally, 9999 is the largest value for any interval. Therefore, it might be required to use a combination of intervals, such as: MONTHS to DAYS in order to receive an answer without an overflow occurring.

CAST Using Intervals

Compliance: ANSI

The CAST function was seen in an earlier chapter as the ANSI method for converting data from one type to another. It can also be used to convert one INTERVAL to another INTERVAL representation. Although the CAST is normally used in the SELECT list, it works in the WHERE clause for comparison reasons.

Below is the syntax for using the CAST with a date:

SELECT CAST (<interval> AS INTERVAL <interval> )  FROM  <table-name> ;<Interval>  = { DAY | DAY TO HOUR | DAY TO MINUTE | DAY TO SECOND |  HOUR |  HOUR TO MINUTE | MINUTE | MINUTE TO SECOND }

The following converts an INTERVAL of 6 years and 2 months to an INTERVAL number of months:

1 Row Returned



Logic seems to dictate that if months can be shown, the years and months should also be available. This request attempts to convert 1300 months to show the number of years and months:

SELECT  CAST(INTERVAL '1300' MONTH AS YEAR TO MONTH)  (Title  'Years & Months') ;  ***  Failure 7453 Interval Field Overflow.

The above failed because the number of months takes more than two digits to hold a number of years greater than 99. The fix is to change the YEAR to YEAR(3) and rerun:

SELECT CAST((INTERVAL  '1202' MONTH) AS INTERVAL YEAR(3) TO MONTH )  (Title  'Years & Months') ;
1 Row Returned

Years & Months


The biggest advantage in using the INTERVAL processing is that SQL written on another system is now compatible with Teradata.

At the same time, care must be taken to use a representation that is large enough to contain the answer. The default is 2 digits and anything larger, 4 digits maximum, must be literally requested. The incorrect size results in an SQL runtime error.


Compatibility: Teradata Extension

When working with dates and times, sometimes it is necessary to determine whether two different ranges have common points in time. Teradata provides a Boolean function to make this test for you. It is called OVERLAPS; it evaluates true, if multiple points are in common, otherwise it returns a false. The syntax of the OVERLAPS is:

SELECT  <literal>  WHERE (<start-date-time>, <end-date-time>) OVERLAPS(<start-date-time>, <end-date-time>)  ;

The following SELECT tests two literal dates and uses the OVERLAPS to determine whether or not to display the character literal:

SELECT  'The dates overlap' (TITLE ")  WHERE (DATE '2001-01-01', DATE '2001-11-30') OVERLAPS(DATE '2001-10-15', DATE '2001-12-31') ;
1 Row Returned

The dates overlap

The literal is returned because both date ranges have from October 15 through November 30 in common.

The next SELECT tests two literal dates and uses the OVERLAPS to determine whether or not to display the character literal:

SELECT  'The dates overlap' (TITLE ")  WHERE  (DATE '2001-01-01', DATE '2001-11-30') OVERLAPS  (DATE  '2001-11-30', DATE '2001-12-31') ;
No Rows Found

The literal was not selected because the ranges do not overlap. So, the common single date of November 30 does not constitute an overlap. When dates are used, 2 days must be involved and when time is used, 2 seconds must be contained in both ranges. The following SELECT tests two literal times and uses the OVERLAPS to determine whether or not to display the character literal:

SELECT  'The times overlap' (TITLE ")  WHERE  (TIME '08:00:00', TIME '02:00:00') OVERLAPS  (TIME  '02:01:00', TIME '04:15:00') ;
1 Row Returned

The times overlap

This is a tricky example and it is shown to prove a point. At first glance, it appears as if this answer is incorrect because 02:01:00 looks like it starts 1 second after the first range ends. However, the system works on a 24-hour clock when a date and time (timestamp) is not used together. Therefore, the system considers the earlier time of 2AM time as the start and the later time of 8 AM as the end of the range. Therefore, not only do they overlap, the second range is entirely contained in the first range.

The following SELECT tests two literal dates and uses the OVERLAPS to determine whether or not to display the character literal:

SELECT  'The times overlap' (TITLE ")  WHERE  (TIME '10:00:00', NULL) OVERLAPS (TIME '01:01:00', TIME '04:15:00') ;
No Rows Found

When using the OVERLAPS function, there are a couple of situations to keep in mind:

  1. A single point in time, i.e. the same date, does not constitute an overlap. There must be at least one second of time in common for TIME or one day when using DATE.
  2. Using a NULL as one of the parameters, the other DATE or TIME constitutes a single point in time versus a range.

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:


  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_MonthFROM Order_table  INNER JOIN Sys_Calendar.CalendarON  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.CalendarON  order_date = calendar_dateINNER  JOIN Week_DaysON  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).