# 17.10 - Example of interval_expression - Advanced SQL Engine - Teradata Database

## Teradata Vantage™ - SQL Date and Time Functions and Expressions

Product
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1211-171K
Language
English (United States)
The definition for interval_expression can be expressed in three forms:
• interval_term
• interval_expression + interval_term
• (date_time_expression - date_time_term) start TO end

This example uses the second definition.

```SELECT (CAST(INTERVAL '125' MONTH AS INTERVAL YEAR(2) TO MONTH))
+ INTERVAL '12' YEAR;```

The interval_expression is INTERVAL '125' MONTH.

The interval_term is INTERVAL '12' YEAR.

The processing involves the following stages:

1. The CAST function converts the interval_expression value of 125 months to 10 years and 5 months.
2. The interval_term amount of 12 years is added to the interval_expression amount, giving 22 years and 5 months.
3. The result is converted to the appropriate data type, which is INTERVAL YEAR(2) TO MONTH, giving '22-05'.

This example uses the third definition for interval_expression.

You must ensure that the values for date_time_expression and date_time_term are comparable.

`SELECT (TIME '23:59:59.99' - CURRENT_TIME(2)) HOUR(2) TO SECOND(2);`

The date_time_expression is TIME '23:59:59.99'.

The date_term is the date_time_primary - CURRENT_TIME(2).

The processing involves the following stages:

1. Assume that the current system time is 18:35:37.83.
2. The HOUR(2) TO SECOND(2) time interval 18:35:37.83 is subtracted from the TIME value 23:59:59.99, giving the result '5:24:22.16'.

Here is another example that uses the third definition for interval_expression to find the difference in minutes between two TIMESTAMP values. First define a table:

```CREATE TABLE BillDateTime
(start_time TIMESTAMP(0)
,end_time TIMESTAMP(0));```

Now, determine the difference in minutes:

```SELECT (end_time - start_time) MINUTE(4)
FROM BillDateTime;```

The processing involves the following stages:

1. The start_time TIMESTAMP value is subtracted from the end_time TIMESTAMP value, giving an interval result.
2. The MINUTE(4) specifies an interval unit of minutes with a precision of four digits, which allows for a maximum of 9999 minutes, or approximately one week.