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";```

 DATE ANSI DATE TIME ANSI TIME TIMESTAMP TIMESTAMP WITH SIX PRECISION SECOND 9/21/2016 9/21/2016 07:06:35 07:06:35 9/21/2016 07:06:35 9/21/2016 07:06:35.950000

## 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```

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.

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```