Skip to main content

Celonis Product Documentation

ADD_MONTHS
Description

ADD_MONTHS adds a given number of months to a given date.

Supported input column types: base_column -> DATE, months_column -> INT

Output column type: DATE

If the input date is last day of the month, the resulting date will be last day of the month.

If a result of type DATE is outside the interval from the year 1400 CE (including) to the year 10000 CE (excluding), it will be mapped to NULL.

Syntax
  ADD_MONTHS ( table.base_column, table.months_column )
NULL handling

If any parameter is NULL, the result is NULL as well.

Example

[1]

Simple example for ADD_MONTHS.

Query

Column1 

         ADD_MONTHS ( "Table1"."Base_Column" , "Table1"."Month_Column" )

Input

Output

Table1

Base_Column : date

Month_Column : int

Mon Jan 01 2018 00:00:00.000

12

Mon Jan 01 2018 00:00:00.000

1

Mon Jan 01 2018 00:00:00.000

0

Mon Jan 01 2018 00:00:00.000

-1

Result

Column1 : date

Tue Jan 01 2019 00:00:00.000

Thu Feb 01 2018 00:00:00.000

Mon Jan 01 2018 00:00:00.000

Fri Dec 01 2017 00:00:00.000

[2]

Leap years behavior with ADD_MONTHS.

Query

Column1 

         ADD_MONTHS ( "Table1"."Column1" , "Table1"."Integer" )

Input

Output

Table1

Column1 : date

Integer : int

Fri Jan 31 2020 00:00:00.000

1

Tue Dec 31 2019 00:00:00.000

2

Mon Dec 31 2018 00:00:00.000

14

Wed Feb 28 2018 00:00:00.000

24

Sat Feb 29 2020 00:00:00.000

14

Result

Column1 : date

Sat Feb 29 2020 00:00:00.000

Sat Feb 29 2020 00:00:00.000

Sat Feb 29 2020 00:00:00.000

Sat Feb 29 2020 00:00:00.000

Fri Apr 30 2021 00:00:00.000

[3]

ADD_MONTHS behavior when input date is the last day of the month. The resulting date is also the end of the month.

Query

Column1 

         ADD_MONTHS ( "Table1"."Column1" , "Table1"."Integer" )

Input

Output

Table1

Column1 : date

Integer : int

Fri Jan 31 2020 00:00:00.000

3

Mon Nov 30 2020 00:00:00.000

2

Result

Column1 : date

Thu Apr 30 2020 00:00:00.000

Sun Jan 31 2021 00:00:00.000