Teradata Date/Time Function

Date and time function one of the most important function in Teradata. So we need to understand the concept very clearly.

Keywords related to the Date and Time

SELECT DATE AS "DATE"
,CURRENT_DATE AS "ANSI DATE"
,TIME AS "TIME"
,CURRENT_TIME AS "ANSI TIME"
,CURRENT_TIMESTAMP(0) "TIMESTAMP"
,CURRENT_TIMESTAMP(6) "TIMESTAMP WITH SIX PRECISION SECOND";

How Teradata stores dates internally?

Teradata stores dates as INTEGER using below formula.

((Year – 1900)*10000) + (Month*100) + Day

So the date 2016-09-21 will store internally as below.

(2016-1900)*10000 + (9*100) + 21 = 1160921

You can verify this using following query.

SELECT CAST(DATE'2016-09-21' AS INTEGER);

*** Query completed. One row found. One column returned.
 *** Total elapsed time was 1 second.

 2016-09-21
-----------
    1160921

Changing the DATEFORM in BTEQ

You can change the display of date form using DATEFORM. There are 2 types of form to display the date. They are INTEGERDATE and ANSIDATE. The default is INTEGERDATE.

INTEGERDATE -> YY/MM/DD

ANSIDATE -> YYYY-MM-DD

set session dateform=ANSIDATE;

 *** Set SESSION accepted.
 *** Total elapsed time was 1 second.

 BTEQ -- Enter your SQL request or BTEQ command:

SELECT DATE;

SELECT DATE;

 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 1 second.

Current Date
------------
  2016-09-21

As Teradata stores dates as INTEGER, we can perform some arithmetic operation on date. Teradata provides some functions to perform these operations.

Add or subtract days from a DATE

SELECT CURRENT_DATE, CURRENT_DATE-30 AS "OLD DATE", CURRENT_DATE+30" AS FUTURE DATE";

*** Query completed. One row found. 3 columns returned.
 *** Total elapsed time was 1 second.

Current Date    OLD DATE  FUTURE DATE
------------  ----------  -----------
  2016-09-21  2016-08-22   2016-10-21

ADD MONTHS command

You can add a month or many months to your date or timestamp column.

SELECT CURRENT_DATE, ADD_MONTHS(CURRENT_DATE,-2) AS "OLD MONTH", ADD_MONTHS(CURRENT_DATE,2) AS "FUTURE MONTH";

*** Query completed. One row found. 3 columns returned.
 *** Total elapsed time was 1 second.

Current Date   OLD MONTH  FUTURE MONTH
------------  ----------  ------------
  2016-09-21  2016-07-21    2016-11-21

The EXTRACT command

The EXTRACT command extracts date part such as year, month and day from date and hour, minute and second from TIME/TIMESTAMP value.

Example

Following examples how to extract year, month and date from date value and hour, minute and second from the TIMESTAMP value.

SELECT EXTRACT(YEAR from DATE);
EXTRACT(YEAR FROM Current Date)
-------------------------------
                          2016

SELECT EXTRACT(MONTH from DATE);
EXTRACT(MONTH FROM Current Date)
--------------------------------
                               9

SELECT EXTRACT(DAY from DATE);
EXTRACT(DAY FROM Current Date)
------------------------------
                            21

SELECT EXTRACT(HOUR from CURRENT_TIMESTAMP(6));
EXTRACT(HOUR FROM Current TimeStamp(6))
---------------------------------------
                                      9

SELECT EXTRACT(MINUTE from CURRENT_TIMESTAMP(6));
EXTRACT(MINUTE FROM Current TimeStamp(6))
-----------------------------------------
                                        7

SELECT EXTRACT(SECOND from CURRENT_TIMESTAMP(6));
EXTRACT(SECOND FROM Current TimeStamp(6))
-----------------------------------------
                                52.260000

The INTERVAL Command

The INTERVAL function is used to perform the arithmetic and conversion operation on DATE and TIME values.

Teradata provides below INTERVAL functions.

1-Label Intervals

• YEAR
• MONTH
• DAY
• HOUR
• MINUTE
• SECOND

2-Label Intervals

• YEAR TO MONTH
• DAY TO HOUR
• DAY TO MINUTE
• DAY TO SECOND
• HOUR TO MINUTE
• HOUR TO SECOND
• MINUTE TO SECOND

Example

The following example adds 2 years to the current date.

SELECT CURRENT_DATE, CURRENT_DATE + INTERVAL '02' YEAR;

 Current Date  (Current Date+ 2)
------------  -----------------
  2016-09-21         2018-09-21

The following example adds 3 years 3 months to the current date.

SELECT CURRENT_DATE, CURRENT_DATE + INTERVAL '03-03' YEAR TO MONTH;

 Current Date  (Current Date+ 3-03)
------------  --------------------
  2016-09-21            2019-12-21

The following example adds 2 hours 5 minutes 10 seconds to the current_timestamp.

SELECT CURRENT_TIMESTAMP, CURRENT_TIMESTAMP + INTERVAL '02:05:10' HOUR TO SECOND;

            Current TimeStamp(6)   (Current TimeStamp(6)+ 2:05:10)
--------------------------------  --------------------------------
2016-09-21 09:57:48.710000-04:00  2016-09-21 12:02:58.710000-04:00

The following example adds 2 days 10 minutes to the current_timestamp.

SELECT CURRENT_TIMESTAMP, CURRENT_TIMESTAMP + INTERVAL '02 00:10' DAY TO MINUTE;

Current TimeStamp(6)   (Current TimeStamp(6)+ 2 00:10)
--------------------------------  --------------------------------
2016-09-21 10:02:05.220000-04:00  2016-09-23 10:12:05.220000-04:00