17.00 - 17.05 - Example: Non-Intuitive Examples - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Date and Time Functions and Expressions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Release Date
June 2020
Content Type
Programming Reference
Publication ID
B035-1211-170K
Language
English (United States)

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.