Teradata - Date/Time Functions

This chapter discusses the date/time functions available in Teradata.

Date Storage

Dates are stored as integer internally using the following formula.

((YEAR - 1900) * 10000) + (MONTH * 100) + DAY

You can use the following query to check how the dates are stored.

SELECT CAST(CURRENT_DATE AS INTEGER);

Since the dates are stored as integer, you can perform some arithmetic operations on them. Teradata provides functions to perform these operations.

EXTRACT

EXTRACT function extracts portions of day, month and year from a DATE value. This function is also used to extract hour, minute and second from TIME/TIMESTAMP value.

Example

Following examples show how to extract Year, Month, Date, Hour, Minute and second values from Date and Timestamp values.

SELECT EXTRACT(YEAR FROM CURRENT_DATE);  
EXTRACT(YEAR FROM Date) 
----------------------- 
        2016  
SELECT EXTRACT(MONTH FROM CURRENT_DATE);  
EXTRACT(MONTH FROM Date) 
------------------------ 
          1        
SELECT EXTRACT(DAY FROM CURRENT_DATE);  
EXTRACT(DAY FROM Date) 
------------------------ 
          1    
       
SELECT EXTRACT(HOUR FROM CURRENT_TIMESTAMP);  
EXTRACT(HOUR FROM Current TimeStamp(6)) 
--------------------------------------- 
                 4      
SELECT EXTRACT(MINUTE FROM CURRENT_TIMESTAMP);  
EXTRACT(MINUTE FROM Current TimeStamp(6)) 
----------------------------------------- 
                 54  
SELECT EXTRACT(SECOND FROM CURRENT_TIMESTAMP);  
EXTRACT(SECOND FROM Current TimeStamp(6)) 
----------------------------------------- 
              27.140000

INTERVAL

Teradata provides INTERVAL function to perform arithmetic operations on DATE and TIME values. There are two types of INTERVAL functions.

Year-Month Interval

YEAR

YEAR TO MONTH

MONTH

Day-Time Interval

DAY

DAY TO HOUR

DAY TO MINUTE

DAY TO SECOND

HOUR

HOUR TO MINUTE

HOUR TO SECOND

MINUTE

MINUTE TO SECOND

SECOND

Example

The following example adds 3 years to current date.

SELECT CURRENT_DATE, CURRENT_DATE + INTERVAL  03  YEAR; 
  Date    (Date+ 3) 
--------  --------- 
16/01/01   19/01/01

The following example adds 3 years and 01 month to current date.

SELECT CURRENT_DATE, CURRENT_DATE + INTERVAL  03-01  YEAR TO MONTH; 
 Date     (Date+ 3-01) 
--------  ------------ 
16/01/01    19/02/01

The following example adds 01 day, 05 hours and 10 minutes to current timestamp.

SELECT CURRENT_TIMESTAMP,CURRENT_TIMESTAMP + INTERVAL  01 05:10  DAY TO MINUTE; 
     Current TimeStamp(6)         (Current TimeStamp(6)+ 1 05:10) 
--------------------------------  -------------------------------- 
2016-01-01 04:57:26.360000+00:00  2016-01-02 10:07:26.360000+00:00