Syntax¶

ADD_MONTHS(<date_or_timestamp_expr>,<num_months_expr>)

Arguments¶

Required:

date_or_timestamp_expr

This is the date or timestamp expression to which you want to adda specified number of months.

num_months_expr

This is the number of months you want to add. This should be aninteger. It may be positive or negative. If the value is anon-integer numeric value (for example, FLOAT) the value will berounded to the nearest integer.

Returns¶

The data type of the returned value is the same as the data type of thefirst parameter. For example, if the input is aDATE, then theoutput is aDATE. If the input is aTIMESTAMP_NTZ, then theoutput is aTIMESTAMP_NTZ.

Usage notes¶

• ADD_MONTHS returns slightly different results thanDATEADD used with aMONTH component:

  • For both ADD_MONTHS andDATEADD, if the result month has fewer days than the original day, the result day of the month is the last day of the result month.

  • For ADD_MONTHS only, if the original day is the last day of the month, the result day of month will be the last day of the result month.

• num_months_expr can be a positive or negative integer to either add or subtract months, respectively.

Examples¶

Add 2 months to a date and cast the date to a timestamp with no time zone:

SELECTADD_MONTHS('2016-05-15'::timestamp_ntz,2)ASRESULT;+-------------------------+| RESULT                  ||-------------------------|| 2016-07-15 00:00:00.000 |+-------------------------+

Copy

Demonstrate preservation of end-of-month information:

• Add one month to the last day of February 2016 (a leap year).

• Subtract one month from the last day of May 2016.

  SELECTADD_MONTHS('2016-02-29'::date,1)ASRESULT;+------------+| RESULT     ||------------|| 2016-03-31 |+------------+

  Copy

  SELECTADD_MONTHS('2016-05-31'::date,-1)ASRESULT;+------------+| RESULT     ||------------|| 2016-04-30 |+------------+

  Copy