15.00 - Scalar Operations on Teradata DATE Values - 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)
Last Update
2018-09-24

Scalar Operations on Teradata DATE Values

The operations of addition and subtraction are allowed as follows, where integer values represent the number of days:

 

Argument 1

Operation

Argument 2

Result

DATE

+

INTEGER

DATE

DATE

-

INTEGER

DATE

INTEGER

+

DATE

DATE

DATE

-

DATE

INTEGER

Adding 90 days, for example, is not identical to adding 3 months, because of the varying number of days in months.

Also, adding multiples of 365 days is not identical to adding years because of leap years.

Note that scalar operations on Teradata DATE expressions are performed using ANSI SQL:2011 data types, so an expression of the type date_expression - numeric_expression is treated as if the numeric_expression component were typed as INTERVAL DAY.

ANSI SQL:2011 DateTime and Interval values have their own set of scalar operations and with the exception of the scalar operations defined here for DATE, do not support the implicit conversions to resolve expressions of mixed data types.

ADD_MONTHS Function

The ADD_MONTHS function provides for adding or subtracting months or years, handling the variable number of days involved.

For details, see “ADD_MONTHS” on page 856.

EXTRACT Function

Use the EXTRACT function to get the year, month, or day from a date. The result has INTEGER data type.

For details, see “EXTRACT” on page 870.