15.00 - ADD_MONTHS - 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

ADD_MONTHS

Purpose  

Adds an integer number of months to a DATE or TIMESTAMP expression and normalizes the result.

Date Syntax

Timestamp Syntax

where:

 

Syntax element …

Specifies …

date_expression

one of the following, to which integer_expression months are to be added:

  • A DATE value enclosed in apostrophes
  • A DATE literal
  • The CURRENT_DATE keyword
  • The DATE keyword
  • A UDT that has an implicit cast that casts between the UDT and a character or DATE type.
  • CURRENT_DATE and DATE specify the current system DATE value.

    timestamp_expression

    one of the following, to which integer_expression months are to be added:

  • A TIMESTAMP literal
  • The CURRENT_TIMESTAMP keyword
  • A UDT that has an implicit cast that casts between the UDT and a character or TIMESTAMP type.
  • CURRENT_TIMESTAMP specifies the current system TIMESTAMP value.

    integer_expression

    the number of integer months to be added to date_expression or timestamp_expression.

    ANSI Compliance

    This is a Teradata extension to the ANSI SQL:2011 standard.

    Rules

    ADD_MONTHS observes the following rules:

  • If either argument of ADD_MONTHS is NULL, then the result is NULL.
  • If the result is not in the range ‘0000-01-01’ to ‘9999-12-31’, then an error is reported.
  • Results of an ADD_MONTHS function that are not valid dates are normalized to ensure that all reported dates are valid.
  • Support for UDTs

     

    IF this argument is a UDT …

    THEN Teradata Database performs implicit type conversion if the UDT has an implicit cast that casts between the UDT and any of the following predefined types …

    date_expression

  • Character
  • Date
  • Timestamp
  • timestamp_expression

    integer_expression

    Numeric

    To define an implicit cast for a UDT, use the CREATE CAST statement and specify the AS ASSIGNMENT clause. For more information on CREATE CAST, see SQL Data Definition Language.

    Implicit type conversion of UDTs for system operators and functions, including ADD_MONTHS, is a Teradata extension to the ANSI SQL standard. To disable this extension, set the DisableUDTImplCastForSysFuncOp field of the DBS Control Record to TRUE. For details, see Utilities: Volume 1 (A-K).

    For more information on implicit type conversion of UDTs, see “Implicit Type Conversions” on page 583.

    Scalar Arithmetic on Months Issues

    Consistent handling of a target month having fewer days than the month in the source date is an important issue for scalar arithmetic on month intervals because the concept of a month has no fixed definition.

    All scalar function operations on dates use the Gregorian calendar. Peculiarities of the Gregorian calendar ensure that arithmetic operations such as adding 90 days (to represent three months) or 730 days (to represent two years) to a DATE value generally do not provide the desired result. For more information, see “Gregorian Calendar Rules” on page 831.

    The ADD_MONTHS function uses an algorithm that lets you add or subtract a number of months to a date_expression or timestamp_expression and to obtain consistently valid results.

    When deciding whether to use the Teradata SQL ADD_MONTHS function or ANSI SQL:2011 DateTime interval arithmetic, you are occasionally faced with choosing between returning a result that is valid, but probably neither desired nor expected, or not returning any result and receiving an error message.

    A third option that does not rely on system-defined functions is to use the Teradata Database-defined Calendar view for date arithmetic. For information, see “CALENDAR View” in Data Dictionary.

    Normalization Behavior of ADD_MONTHS

    The standard approach to interval month arithmetic is to increment MONTH and YEAR values as appropriate and retain the source value for DAY. This is a problem for the case when the target DAY value is smaller than the source DAY value from the source date.

    For example, what approach should be taken to handle the result of adding one MONTH to a source DATE value of ‘1999-01-31’? Using the standard approach, the answer would be ‘1999-02-31’, but February 31 is not a valid date.

    The behavior of ADD_MONTHS is equivalent to that of the ANSI SQL:2011 compliant operations DATE ± INTERVAL ‘n’ MONTH and TIMESTAMP ± INTERVAL ‘n’ MONTH with one important difference.

    The difference between these two scalar arithmetic operations is their behavior when a non valid date value is returned by the function.

  • ANSI SQL:2011 arithmetic returns an error.
  • ADD_MONTHS arithmetic makes normative adjustments and returns a valid date.
  • Definition of Normalization

    The normalization process is explained more formally as follows.

    When the DAY field of the source date_expression or timestamp_expression is greater than the resulting target DAY field, ADD_MONTHS sets DD equal to the last day of the month + n to normalize the reported date or timestamp.

    Define date_expression as ‘YYYY-MM-DD’ for simplicity.

    For a given date_expression, you can then express the syntax of ADD_MONTHS as follows.

       ADD_MONTHS('YYYY-MM-DD' , n)

    Recalling that n can be negative, and substituting ‘YYYY‑MM‑DD’ for date_expression, you can redefine ADD_MONTHS in terms of ANSI SQL:2011 dates and intervals as follows.

       ADD_MONTHS('YYYY-MM-DD', n) = 'YYYY-MM-DD' ± INTERVAL 'n' MONTH

    The equation is true unless a non valid date such as 1999-09-31 results, in which case the ANSI expression traps the non valid date exception and returns an error.

    ADD_MONTHS, on the other hand, processes the exception and returns a valid, though not necessarily expected, date. The algorithm ADD_MONTHS uses to produce its normalized result is as follows, expressed as pseudocode.

       WHEN
       DD > last_day_of_the_month(MM+n)
       THEN SET
       DD = last_day_of_the_month(MM+n)

    This property is also true for the date portion of any timestamp_expression.

    Note that normalization produces valid results for leap years.

    Non-Intuitive Results of ADD_MONTHS

    Because of the normalization made by ADD_MONTHS, many results of the function are not intuitive, and their inversions are not always symmetrical. For example, compare the results of “Example 5” on page 860 with the results of “Example 7” on page 861.

    This is because the function always produces a valid date, but not necessarily an expected date. Correctness in the case of interval month arithmetic is a relative term. Any definition is arbitrary and cannot be generalized, so the word ‘expected’ is a better choice for describing the behavior of ADD_MONTHS.

    The following SELECT statements return dates that are both valid and expected:

       SELECT ADD_MONTHS ('1999-08-15' , 1);

    This statement returns 1999-09-15.

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

    This statement returns 1999-08-30.

    The following SELECT statement returns a valid date, but its ‘correctness’ depends on how you choose to define the value ‘one month.’

       SELECT ADD_MONTHS ('1999-08-31' , 1);

    This statement returns 1999-09-30, because September has only 30 days and the non-normalized answer of 1999-09-31 is not a valid date.

    ADD_MONTHS Summarized

    ADD_MONTHS returns a new date_expression or timestamp_expression with YEAR and MONTH fields adjusted to provide a correct date, but a DAY field adjusted only to guarantee a valid date, which might not be a date you expect intuitively.

    If this behavior is not acceptable for your application, use ANSI SQL:2011 DateTime interval arithmetic instead. For more information, see “ANSI Interval Expressions” on page 834.

    Remember that ADD_MONTHS changes the DAY value of the result only when a non valid date_expression or timestamp_expression would otherwise be reported.

    For examples of this behavior, see the example set listed under “Non-Intuitive Examples” on page 860.

    Intuitive Examples

    “Example 1” through “Example 5” are simple, intuitive examples of the ADD_MONTHS function. All results are both valid and expected.

    Example  

    This statement returns the current date plus 13 years.

       SELECT ADD_MONTHS (CURRENT_DATE, 12*13);

    Example  

    This statement returns the date 6 months ago.

       SELECT ADD_MONTHS (CURRENT_DATE, -6);

    Example  

    This statement returns the current TIMESTAMP plus four months.

       SELECT ADD_MONTHS (CURRENT_TIMESTAMP, 4);

    Example  

    This statement returns the TIMESTAMP nine months from January 1, 1999. Note the literal form, which includes the keyword TIMESTAMP.

       SELECT ADD_MONTHS (TIMESTAMP '1999-01-01 23:59:59', 9);

    Example  

    This statement adds one month to January 30, 1999.

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

    The result is 1999-02-28.

    Non-Intuitive Examples

    “Example 6” through “Example 10” 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  6

    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  7

    This statement performs the converse of the ADD_MONTHS function in “Example 5” on page 860.

    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 5.”

    Example  8

    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  9

    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  10

    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.