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