15.00 - Arithmetic Operators and ANSI DateTime and Interval Data Types - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)

Arithmetic Operators and ANSI DateTime and Interval Data Types

Operations on ANSI DateTime and Interval values can include the scalar arithmetic operators +, -, *, and /. However, the operators are only valid on specific combinations of DateTime and Interval values.

Arithmetic Operators and Result Types

The following arithmetic operations are permitted for DateTime and Interval data types:

 

First Value Type

Operator

Second Value Type

Result Type

DateTime

-

DateTime

Interval

DateTime

+

Interval

DateTime

DateTime

-

Interval

DateTime

Interval

+

DateTime

DateTime

Interval

+

Interval

Interval

Interval

-

Interval

Interval

Interval

*

Number

Interval

Interval

/

Number

Interval

Number

*

Interval

Interval

Adding or Subtracting Numbers from DATE

Teradata SQL extends the ANSI SQL:2011 standard to allow the operations of adding or subtracting a number of days from an ANSI DATE value.

Teradata SQL treats the number as an INTERVAL DAY value.

For more information, see “DATE and Integer Arithmetic” on page 846.

Calculating the Difference Between Two DateTime Values

Teradata Database calculates the interval difference between two DATE, TIME or TIMESTAMP values according to the ANSI SQL standard. Units smaller than the unit of the result are ignored when calculating the interval value.

For example, when computing the difference in months for two DATE values, the day values in each of the two operands are ignored. Similarly when computing the difference in hours for two TIMESTAMP values, the minutes and the seconds values of the operands are ignored.

Example  

The following query calculates the difference in days between the two DATE values.

   SELECT (DATE '2007-05-10' - DATE '2007-04-28') DAY;

The result is the following:

   (2007-05-10 - 2007-04-28) DAY
   -----------------------------
   12

The following query calculates the difference in months between the two DATE values.

   SELECT (DATE '2007-05-10' - DATE '2007-04-28') MONTH;

The result is the following:

   (2007-05-10 - 2007-04-28) MONTH
   -------------------------------
   1

There is a difference of 12 days between the two dates, which does not constitute one month. However, Teradata Database ignores the day values during the calculation and only considers the month values, so the result is an interval of one month indicating the difference between April and May.

Example : Add Interval to DATE

The following example adds an Interval value to a DateTime value:

   CREATE TABLE Subscription
   (id CHARACTER(13)
   ,subscribe_date DATE
   ,subscribe_interval INTERVAL MONTH(4));
   
   INSERT Subscription (subscribe_date, subscribe_interval)
   VALUES (CURRENT_DATE, INTERVAL ’24’ MONTH);
   
   SELECT subscribe_date + subscribe_interval FROM Subscription;
 

The result is a DateTime value.