Chapter 19
Date Functions
“An inch of time cannot be bought with an inch of gold.”
Table of Contents Chapter 19 – Date Functions
– Dates are stored Internally as INTEGERS from a Formula
– Date, Time, and Timestamp Keywords
– INTEGER Date Vs ANSIDATE is how the Date is Displayed
– DATEFORM
– Changing the DATEFORM in Client Utilities such as BTEQ
– Date, Time, and Timestamp Recap
– Add or Subtract Days from a date
– A Summary of Math Operations on Dates
– Using a Math Operation to find your Age in Years
– Find What Day of the week you were Born
– Using the ADD MONTHS Command to Add 1-Year
– Using the ADD MONTHS Command to Add 5-Years
– CURRENT DATE and Math to get Temporal Functions
– CAST the Date of January 1, 2011 and the Year 1800
– How to really use the Sys Calendar.Calendar
– Storing TIME With TIME ZONE Internally
– Storing Timestamp Internally
– Storing Timestamp with TIME ZONE Internally
– Storing Date, Time, Timestamp with Zone Internally
– Creating a Sample Table for Time Zone Examples
– Inserting Rows in the Sample Table for Time Zone Examples
– Selecting the Data from our Time Zone Table
– Normalizing our Time Zone Table with a CAST
– Intervals for Date, Time and Timestamp
– Interval Data Types and the Bytes to Store Them
– The Basics of a Simple Interval
– Troubleshooting The Basics of a Simple Interval
– A Complex Time Interval Example using CAST
– A Complex Time Interval Example using CAST
– An OVERLAPS Example that Returns No Rows
– The OVERLAPS Command using TIME
– The OVERLAPS Command using a NULL Value
Dates are stored Internally as INTEGERS from a Formula
INTEGERDATE = ((Year – 1900) * 10000) + (Month * 100) + Day
/* Example – Tom's Birthday January 10, 1959 */
/* Example – Tom's Birthday January 10, 1999 */
990110
/* Example – Tom's Birthday January 10, 2000 */
1000110
The way the Smart Calendar works so well is that it stores EVERY date in Teradata as something known as an INTEGERDATE.
Date, Time, and Timestamp Keywords
SELECT Date AS “Date”
,Current_Date AS ANSI_Date
,Time AS “Time”
,Current_Time AS ANSI_Time
,Current_Timestamp(6) AS ANSI_Timestamp
Answer Set
There's no keyword Timestamp, but only ANSI's Current_Timestamp
Above are the keywords you can utilize to get the date, time, or timestamp. These are reserved words that the system will deliver to you when requested.
INTEGER Date Vs ANSIDATE is how the Date is Displayed
SELECT Date AS “Date”
,Current_Date AS ANSI_Date
INTEGERDATE (YY/MM/DD)
June 30, 2012
| Date | ANSI_Date |
| 12/06/30 | 12/06/30 |
ANSIDATE (YY/MM/DD)
June 30, 2012
| Date | ANSI_Date |
| 2012-06-30 | 2012-06-30 |
NEXUS Query Chameleon MM-DD-YYYY
| Date | ANSI_Date |
| 06-30-2012 | 06-30-2012 |
Teradata in release V2R3 defaulted to a display of YY/MM/DD. This is called the INTEGERDATE. This can be changed to ANSIDATE, which is YYYY-MM-DD for a specific session or by Default if the DBA changes the DATEFORM in DBS Control. This has nothing to do with how the date is stored internally. It has to do with the display of dates when using any ODBC tool or load utility. Above are some examples.
DATEFORM
DATEFORM Controls the default display of dates.
DATEFORM display choices are either INTEGERDATE or ANSIDATE.
INTEGERDATE is (YY/MM/DD) and ANSIDATE is (YYYY-MM-DD).
DATEFORM is the expected format for import and export of dates in Load Utilities.
Can be over-ridden by USER or within a Session at any time.
The Default can be changed by the DBA by changing the DATEFORM in DBSControl.
INTEGERDATE (YY/MM/DD)
June 30, 2012
| Date | ANSI_Date |
| 12/06/30 | 12/06/30 |
ANSIDATE (YYYY-MM-DD)
June 30, 2012
| Date | ANSI_Date |
| 2012-06-30 | 2012-06-30 |
Teradata in release V2R3 defaulted to a display of YY/MM/DD. This is called the INTEGERDATE. This can be changed to ANSIDATE, which is YYYY-MM-DD for a specific session or by Default if the DBA changes the DATEFORM in DBS Control. This has nothing to do with how the date is stored internally. It has to do with the display of dates when using any ODBC tool or load utility.
Changing the DATEFORM in Client Utilities such as BTEQ
Date, Time, and Timestamp Recap
SELECT Date AS “Date”
,Current_Date AS ANSI_Date
INTEGERDATE (YY/MM/DD)
June 30, 2012
| Date | ANSI_Date |
| 12/06/30 | 12/06/30 |
ANSIDATE (YYYY-MM-DD)
June 30, 2012
| Date | ANSI_Date |
| 2012-06-30 | 2012-06-30 |
Dates are converted to an integer through a formula before being stored.
Dates are displayed by default as INTEGERDATE YY-MM-DD.
The DBA can set up the system to display as ANSIDATE YYYY-MM-DD.
Keywords Date or Current_Date will return the date automatically.
Time, Current_Time and Current_Timestamp are keywords.
The Nexus Query Chameleon displays dates as MM-DD-YYYY.
Timestamp Differences
SELECT Current_Timestamp(0) AS Col1
,Current_Timestamp(6) AS Col2
Answer Set
A timestamp has the date separated by a space and the time. In our second example we have asked for 6 milliseconds.
Troubleshooting Timestamp
SELECT Timestamp(0) AS Col1
, Timestamp(6) AS Col2
Error
There is Date and Current_Date (both work).
There is Time and Current_Time (both work).
There is NO Timestamp, but only Current_Timestamp!
There is NO Timestamp command, but only ANSI's Current_Timestamp!
Add or Subtract Days from a date
SELECT Order_Date
,Order_Date + 60 as “Due Date”
,Order_Total
,''Due date'' –10 as Discount
,Order_Total *.98 (FORMAT ‘$$$$,$$$.99', Title 'Discounted')
FROM Order_Table
ORDER BY 1 ;
When you add or subtract from a Date you are adding/subtracting Days
Because Dates are stored internally on disk as integers it makes it easy to add days to the calendar. In the query above we are adding 60 days to the Order_Date.
A Summary of Math Operations on Dates
Let's find the number of days Tera-Tom has been alive since his last birthday.
SELECT (1120110(date)) – (590110 (date)) (Title ‘Tera-Tom’'s Age In Days’);
| Tera-Tom's Age In Days |
| 19358 |
Below is the same exact query, but with a clearer example of the dates.
SELECT (‘1959-01-10’(date)) – (‘2012-01-10’ (date)) (Title ‘Tera-Tom''s Age In Days’);
| Tera-Tom's Age In Days |
| 19358 |
A DATE – DATE is an interval of days between dates. A DATE + or – Integer = Date. Both queries above perform the same function, but the top query uses the internal date functions and the query on the bottom does dates the traditional way.
Using a Math Operation to find your Age in Years
Let's find the number of days Tera-Tom has been alive since his last birthday.
SELECT (1120110(date)) – (590110 (date)) (Title ‘Tera-Tom’'s Age In Days'),
| Tera-Tom's Age In Days |
| 19358 |
Let's find the number of years Tera-Tom has been alive since his last birthday.
SELECT (1120110(date)) – (590110 (date))) / (Title ‘Tera-Tom’'s Age In Days'),
| Tera-Tom's Age In Days |
| 53 |
A DATE – DATE is an interval of days between dates. A DATE + or – Integer = Date. Both queries above perform the same function, but the top query uses the internal date functions and the query on the bottom does dates the traditional way.
Find What Day of the week you were Born
Let's find the actual day of the week Tera-Tom was born
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, Tom was born on a Saturday.
The ADD_MONTHS Command
SELECT Order_Date
,Add_Months (Order_Date,2) as “Due Date”
,Order_Total
FROM Order_Table ORDER BY 1 ;
| Order_Date | Due Date | Order_Total |
| 05/04/1998 | 07/04/1998 | 12347.53 |
| 01/01/1999 | 03/01/1999 | 8005.91 |
| 09/09/1999 | 11/09/1999 | 23454.84 |
| 10/01/1999 | 12/01/1999 | 5111.47 |
| 10/10/1999 | 12/10/1999 | 15231.62 |
This is the Add_Months Command. What you can do with it is add a month or many months your columns date. Can you convert this to one year?
Using the ADD_MONTHS Command to Add 1-Year
There is no Add_Year command, so put in 12 months for 1-year
The Add_Months command adds months to any date. Above we used a great technique that would give us 1-year. Can you give me 5-years?
Using the ADD_MONTHS Command to Add 5-Years
In this example we multiplied 12 months times 5 for a total of 5 years!
Above you see a great technique for adding multiple years to a date. Can you now SELECT only the orders in September?
The EXTRACT Command
SELECT Order_Date
,Add_Months (Order_Date,12 * 5) as “Due Date”
,Order_Total
FROM Order_Table
WHERE EXTRACT(Month from Order_Date) = 09
ORDER BY 1 ;
The EXTRACT command extracts portions of Date, Time, and Timestamp.
This is the Extract command. It extracts a portion of the date and it can be used in the SELECT list or the WHERE Clause, or the ORDER BY Clause!
EXTRACT from DATES and TIME
SELECT Current_Date
,EXTRACT(Year from Current_Date) as Yr
,EXTRACT(Month from Current_Date) as Mo
,EXTRACT(Day from Current_Date) as Da
,Current_Time
,EXTRACT(Hour from Current_Time) as Hr
,EXTRACT(Minute from Current_Time) as Mn
,EXTRACT(Second from Current_Time) as Sc
,EXTRACT(TIMEZONE_HOUR from Current_Time) as Th
,EXTRACT(TimeZONE_MINUTE from Current_Time) as Tm
Answer Set
Just like the Add_Months, the EXTRACT Command is a Temporal Function or a Time-Based Function.
CURRENT_DATE and Math to get Temporal Functions
SELECT Current_Date
,EXTRACT(Year from Current_Date) as Yr
,EXTRACT(Month from Current_Date) as Mo
,EXTRACT(Day from Current_Date) as Da
,Current_Date / 10000 +1900 as YrMath
,(Current_Date / 100) Mod 100 as MoMath
,Current_Date Mod 100 as DayMath ;
Math can be used to extract portions of a Date!
Answer Set
The Extract Temporal Function can be used to extract a portion of a date. As you can see, Basic Arithmetic accomplish the same thing.
CAST the Date of January 1, 2011 and the Year 1800
SELECT
cast('2011-01-01' as date) as ANSI_Literal
,cast( 1110101 as date) as INTEGER_Literal
,cast('11-01-01' as date) as YY_Literal
,cast(Date '2011-01-01' as Integer) as Dates_Stored
,cast(Date '1800-01-01' as Integer) as Dates_1800s
Answer Set
The Convert And Store (CAST) command is used to give columns a different data type temporarily for the life of the query. Notice our dates and how their stored.
The System Calendar
Teradata systems have a table called Caldates.
Caldates has only one column in it called Cdates.
Cdates is a date column that contains a row for each date starting from January 1, 1900 to December 31, 2100.
No user can access the table Caldates directly.
Views in the Sys_Calendar database accesses Caldates.
A view called Calendar is how USER's work with the calendar.
Users use Sys_Calendar.Calendar for advanced dates.
In every Teradata system, they have something known as a System Calendar (or as Teradata calls it Sys_Calendar.Calendar). Get ready for AWESOME!
Calendar_Date = 01/10/1959'
day_of_week = 7 (Sunday = 1)
day_of_month = 10
day_of_year = 10
day_of_Calendar = 21559 (since Jan 1, 1900) weekday_of_month = 2 week_of_month = 1 (0 for partial week for any month not starting with Sunday) week_of_year = 1 week_of_calendar = 3079 (since Jan 1, 1900) month_of_quarter = 1 month_of_year = 1 month_of_calendar = 709 (since Jan 1, 1900) quarter_of_year = 1
quarter_of_calender = 237 (since Jan 1, 1900) year_of_calendar = 1959
Tera-Tom was born on a Saturday! It was the first full week of the month, the first full week of the year and it was the first quarter of the year!
How to really use the Sys_Calendar.Calendar
We just brought back all Orders from the Order_Table that were
purchased on a Friday in the 4th Quarter, during the
1st partial week. This means no Sunday seen yet for that month.
Above is the perfect example of how you can utilize the Sys_Calendar.Calendar to join to any date field and then expand your search options.
Storing Dates Internally
CREATE SET TABLE TIMEZONE_table ,FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL ,
CHECKSUM = DEFAULT
(Date_col Date,
TIME_col TIME(6),
TIMETIMEZONE_col TIME(6) WITH TIME ZONE,
TIMESTAMP_col TIMESTAMP(6),
TIMEZONE_col TIMESTAMP(6) WITH TIME ZONE)
UNIQUE PRIMARY INDEX ( TIMEZONE_col );
DATE ‘1999-01-10’ is stored as 990110
DATE ‘2000-01-10’ is stored as 1000110
4-bytes store Date_col internally because dates are considered a 4-byte integer.
Storing Time Internally
CREATE SET TABLE TIMEZONE_table ,FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(Date_col Date,
TIME_col TIME(6),
TIMETIMEZONE_col TIME(6) WITH TIME ZONE,
TIMESTAMP_col TIMESTAMP(6),
TIMEZONE_col TIMESTAMP(6) WITH TIME ZONE)
UNIQUE PRIMARY INDEX ( TIMEZONE_col );
Time(n) stored as HHMMSS.nnnnnn
It takes 6 bytes to store Time_col internally.
Storing TIME With TIME ZONE Internally
CREATE SET TABLE TIMEZONE_table ,FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL ,
CHECKSUM = DEFAULT
(Date_col Date,
TIME_col TIME(6),
TIMETIMEZONE_col TIME(6) WITH TIME ZONE,
TIMESTAMP_col TIMESTAMP(6),
TIMEZONE_col TIMESTAMP(6) WITH TIME ZONE)
UNIQUE PRIMARY INDEX ( TIMEZONE_col );
Time(n) WITH ZONE stored as HHMMSS.nnnnnn+HHMM
It takes 8 bytes to store TimeTimezone_col internally.
Storing Timestamp Internally
CREATE SET TABLE TIMEZONE_table ,FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL ,
CHECKSUM = DEFAULT
(Date_col Date,
TIME_col TIME(6),
TIMETIMEZONE_col TIME(6) WITH TIME ZONE,
TIMESTAMP_col TIMESTAMP(6),
TIMEZONE_col TIMESTAMP(6) WITH TIME ZONE)
UNIQUE PRIMARY INDEX ( TIMEZONEscol );
TimeStamp(n) stored as YYMMDDHHMMSS.nnnnnn
It takes 10 bytes to store TimeStamp_col internally.
Storing Timestamp with TIME ZONE Internally
CREATE SET TABLE TIMEZONE_table ,FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL ,
CHECKSUM = DEFAULT
(Date_col Date,
TIME_col TIME(6),
TIMETIMEZONE_col TIME(6) WITH TIME ZONE,
TIMESTAMP_col TIMESTAMP(6),
TIMEZONE_col TIMESTAMP(6) WITH TIME ZONE)
UNIQUE PRIMARY INDEX ( TIMEZONEscol );
TimeStamp(n) With Zone stored as
YYMMDDHHMMSS.nnnnnn+HHMM
It will take 12 bytes to store Timezone_col internally.
Storing Date, Time, Timestamp with Zone Internally
CREATE SET TABLE TIMEZONE_table ,FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL ,
CHECKSUM = DEFAULT
(Date_col Date,
TIME_col TIME(6),
TIMETIMEZONE_col TIME(6) WITH TIME ZONE,
TIMESTAMP_col TIMESTAMP(6),
TIMEZONE_col TIMESTAMP(6) WITH TIME ZONE)
UNIQUE PRIMARY INDEX ( TIMEZONE_col );
| Date | Stored Internally | 4 Bytes |
| Time(n) | Stored Internally | 6 Bytes |
| Time(n) With Zone | Stored Internally | 8 Bytes |
| Timestamp(n) | Stored Internally | 10 Bytes |
| Timestamp(n) with zone | Stored Internally | 12 Bytes |
Each data type increase their internal storage by 2 bytes.
Time Zones
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.
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).
Setting Time Zones
A Time Zone should be established for the system and every user in each different time zone.
Setting the system default time zone is done by the DBA in the DBSControl record:
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 an explicit value:
CREATE USER Tera-Tom
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 without requiring a logoff and logon
Seeing your Time Zone
Not all output
is displayed
above from the
HELP Session
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. Teradata converts all TIME and TIMESTAMP values to Universal Time Coordinate (UTC) prior to storing them. All operations, including hashing, collation, and comparisons that act on TIME and TIMESTAMP values are performed using their UTC forms. This will allow users to CAST the information to their local times.
Creating a Sample Table for Time Zone Examples
CREATE TABLE Tstamp_Test
(
TS_Zone CHAR(3)
,TS_with_Zone TIMESTAMP(6) WITH TIME ZONE
,TS_Without_Zone TIMESTAMP(6)
)
UNIQUE PRIMARY INDEX ( TS_Zone );
Not all output
is displayed
above from the
HELP Session
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.
Inserting Rows in the Sample Table for Time Zone Examples
Enter your logon or BTEQ Command:
.logon localtd/dbc
Password: ***********
Logon successfully completed
BTEQ – Enter your DBC/SQL request or BTEQ command:
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’);
Selecting the Data from our Time Zone Table
SELECT * FROM Tstamp_Test
Our Insert statements were done at 08:12:00 exactly. Notice the Time Zone offsets in the column TS_with_Zone and how their not there for the column TS_Without_Zone. Teradata converts all TIME and TIMESTAMP values to Universal Time Coordinate (UTC) prior to storing them. All operations, including hashing, collation, and comparisons that act on TIME and TIMESTAMP values are performed using their UTC forms. This will allow users to CAST the information to their local times.
Normalizing our Time Zone Table with a CAST
SELECT TS_Zone, TS_with_Zone
,CAST(TS_with_Zone AS TIMESTAMP(6)) AS T_Normal
FROM Tstamp_Test ORDER BY 3 ;
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.
Intervals for Date, Time and Timestamp
Interval Chart
| Simple Intervals | More involved Intervals |
| YEAR | DAY TO HOUR |
| MONTH | DAY TO MINUTE |
| DAY | DAY TO SECOND |
| HOUR | HOUR TO MINUTE |
| MINUTE | HOUR TO SECOND |
| SECOND | MINUTE TO SECOND |
To make Teradata SQL more ANSI compliant and compatible with other RDBMS SQL, Teradata 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.
Interval Data Types and the Bytes to Store Them
Interval Chart
| Bytes | Data Type | Comments |
| 2 | INTERVAL YEAR | |
| 4 | INTERVAL YEAR TO MONTH | |
| 2 | INTERVAL MONTH | |
| 2 | INTERVAL MONTH TO DAY | |
| 2 | INTERVAL DAY | |
| 8 | INTERVAL DAY TO MINUTE | |
| 10/12 | INTERVAL DAY TO SECOND | 10 for 32-bit systems; 12 for 64-bit |
| 2 | INTERVAL HOUR 2 | |
| 4 | INTERVAL HOUR TO MINUTE 4 | |
| 8 | INTERVAL HOUR TO SECOND 8 | |
| 2 | INTERVAL MINUTE 2 | |
| 6/8 | INTERVAL MINUTE TO SECOND | 6 for 32-bit systems; 8 for 64-bit |
| 6/8 | INTERVAL SECOND | 6 for 32-bit systems; 8 for 64-bit |
The Basics of a Simple Interval
SELECT Current_Date as Our_Date
,Current_Date + Interval '1' Day as Plus_1_Day
,Current_Date + Interval '3' Month as Plus_3_Months
,Current_Date + Interval '5' Year as Plus_5_Years
In the example SQL above we take a simple date and add 1 day, 3 months and 5 years. Notice that our current_date is 06/18/2012 and that our intervals come out perfectly.
Troubleshooting The Basics of a Simple Interval
SELECT Date '2012-01-29' as Our_Date
,Date '2012-01-29' + INTERVAL '1' Month as Leap_Year
| Our_Date | Leap_Year |
| 01/29/2012 | 02/29/2012 |
SELECT Date '2011-01-29' as Our_Date
,Date '2011-01-29' + INTERVAL '1' Month as Leap_Year
Error – Invalid Date
The first example works because we added 1 month to the date ‘2012-01-29’ and we got ‘2012-02-29’. Because this was leap year there actually is a date of February 29, 2012. The next example is the real point. We have a date of ‘2011-01-29’ and we add 1-month to that, but there is no February 29th in 2011 so the query fails.
Interval Arithmetic Results
DATE and TIME arithmetic Results using intervals:
To use DATE and TIME arithmetic, it is important to keep in mind the results of various operations. The above chart is you Interval guide.
A Date Interval Example
SELECT (DATE '1999-10-01' – DATE '1988-10-01') DAY AS Actual_Days ;
ERROR – Interval Field Overflow
The Error occurred because the
default for all intervals is 2 digits.
The default for all intervals is 2 digits. We received an overflow error because the Actual_Days is 4017. The second example works because we demanded the output to be 4 digits (the maximum for intervals).
A Time Interval Example
SELECT (TIME '12:45:01' – TIME '10:10:01') HOUR AS Actual_Hours
,(TIME '12:45:01' – TIME '10:10:01') MINUTE 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
ERROR – Interval Field Overflow
The default for all intervals is 2 digits, but notice in the top example we put in 3 digits for Minute, 4 digits for Second and 4,4 digits for the Acutal_Seconds4. If we had not we would have received an overflow error as in the bottom example.
A – DATE Interval Example
SELECT Current_Date,
INTERVAL -'2' YEAR + CURRENT_DATE as Two_years_Ago;
| Date | Two_Year_Ago |
| 06/18/2012 | 06/18/2010 |
The above Interval example uses a –’2’ to go back in time.
A Complex Time Interval Example using CAST
Below is the syntax for using the CAST with a date:
SELECT CAST (<interval> AS INTERVAL <interval> )
FROM <table-name> ;
The following converts an INTERVAL of 6 years and 2 months to an INTERVAL number of months:
SELECT CAST( (INTERVAL '6-02' YEAR TO MONTH) AS INTERVAL MONTH );
| 6-02 |
| 74 |
The CAST function (Convert And Store) is 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.
A Complex Time Interval Example using CAST
This request attempts to convert 1300 months to show the number of years and months. Why does it fail?
SELECT CAST(INTERVAL '1300' MONTH AS INTERVAL YEAR TO MONTH) (Title ‘Years & Months’) ;
ERROR
SELECT CAST(INTERVAL ‘1300' MONTH as interval YEAR(3) TO MONTH) ;
| Years & Month |
| 108-04 |
The top query failed because the INTERVAL result defaults to 2-digits and we have a 3-digit answer for the year portion (108). The bottom query fixes that specifying 3-digits. The biggest advantage in using the INTERVAL processing is that SQL written on another system is now compatible with Teradata.
The OVERLAPS Command
Compatibility: Teradata Extension
The syntax of the OVERLAPS is:
SELECT <literal>
WHERE (<start-date-time>, <end-date-time>) OVERLAPS
(<start-date-time>, <end-date-time>) ;
SELECT 'The Dates Overlap' (TITLE ' ')
WHERE (DATE '2001-01-01', DATE '2001-11-30') OVERLAPS
(DATE '2001-10-15', DATE '2001-12-31'),
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 literal is returned because both date ranges have from October 15 through November 30 in common.
An OVERLAPS Example that Returns No Rows
SELECT 'The dates overlap' (TITLE ' ')
WHERE (DATE '2001-01-01', DATE '2001-11-30') OVERLAPS
(DATE '2001-11-30', DATE '2001-12-31') ;
The above SELECT example tests two literal dates and uses the OVERLAPS to determine whether or not to display the character literal.
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 OVERLAPS Command using TIME
SELECT 'The Times Overlap' (TITLE ' ')
WHERE (TIME '08:00:00', TIME '02:00:00') OVERLAPS
(TIME '02:01:00', TIME '04:15:00') ;
The above SELECT example tests two literal times and uses the OVERLAPS to determine whether or not to display the character literal.
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 OVERLAPS Command using a NULL Value
SELECT 'The Times Overlap' (TITLE ' ')
WHERE (TIME '10:00:00', NULL) OVERLAPS (TIME '01:01:00', TIME '04:15:00')
The above SELECT example tests two literal dates and uses the OVERLAPS to determine whether or not to display the character literal:
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.