Example: Non-Intuitive Examples - Analytics Database - Teradata Vantage

SQL Date and Time Functions and Expressions

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-10-30
dita:mapPath
cpk1628111786971.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
pxz1544241488545
lifecycle
latest
Product Category
Teradata Vantage™

The following examples illustrate how the results of an ADD_MONTHS function are not always what you might expect them to be when the value for DAY in date_expression or the date component of timestamp_expression is 29, 30, or 31.

All examples use a date_expression for simplicity. In every case, the function behaves as designed.

Example: Querying When the Date is Invalid

The result of the SELECT statement in this example is a date in February, 1996. The result would be February 31, 1996 if that were a valid date, but because February 31 is not a valid date, ADD_MONTHS normalizes the answer.

That answer, because the DAY value in the source date is greater than the last DAY value for the target month, is the last valid DAY value for the target month.

SELECT ADD_MONTHS ('1995-12-31', 2);

The result of this example is 1996-02-29.

Note that 1996 was a leap year. If the interval were 14 months rather than 2, the result would be ' 1997-02-28 '.

Example: Querying for the Month Prior to the Current Date

This statement performs the converse of the ADD_MONTHS function in Example: Querying for the Current Date Plus One Month.

You might expect it to return ‘1999-01-30’, which is the source date in that example, but it does not.

SELECT ADD_MONTHS ('1999-02-28' , -1);

ADD_MONTHS returns the result 1999-01-28.

The function performs as designed and this result is not an error, though it might not be what you would expect from reading Example: Querying for the Current Date Plus One Month.

Example: Querying for the Current Date Plus One Month for Month Ending on the 28th

You might expect the following statement to return ‘1999-03-31’, but it does not.

SELECT ADD_MONTHS ('1999-02-28' , 1);

ADD_MONTHS returns the result 1999-03-28.

Example: Querying for the Current Date Plus One Month for Month Ending on the 30th

You might expect the following statement to return ‘1999-03-31’, but it does not.

SELECT ADD_MONTHS ('1999-04-30' , -1);

ADD_MONTHS returns the result 1999-03-30.

Example: Querying for the Current Date Plus One Month for Month Ending on the 30th

You might expect the following statement to return ' 1999-05-31 ', but it does not.

SELECT ADD_MONTHS ('1999-04-30' , 1);

ADD_MONTHS returns the result 1999-05-30.