ADD_MONTHS macro

The ADD_MONTHS macro is available in Unica Campaign. For ORACLE, DB2 and MSSQLServer databases execution is preferred on the database instead of Campaign Server.

Syntax

ADD_MONTHS(months, date_string [, input_format[,DB]])

Parameters

months

An integer that represents a number of months to add to the date_string.

date_string

A text string representing a valid date, in the format DELIM_M_D_Y, or in the format specified by the optional input_format argument.

input_format

The format that will be used for the calculated date. For a list of supported date formats, see the DATE_FORMAT function. Note that the input_format determines both the format of the input string and also the format of the output string.

DB

It is an optional parameter. The macro execution is preferred on the database for ORACLE/DB2/MSSQLServer, even if DB parameter is not specified. For rest of the database types, the behaviour remains the same i.e. execution on campaign server.

Execution will implicitly happen on database if the expression contains database column.

Execution will happen on campaign server if the expression contains all non database columns. e.g. UCGF or date strings etc. To force the execution on database, include DB parameter. Please note in order to be able to specify DB parameter it’s a must to use input_format too.

Description

ADD_MONTHS returns a date after adding the specified number of months to the specified date_string. The date will be returned in the default format (DELIM_M_D_Y) or the format specified by the optional input_format argument. If you want a different format as the output, use DATE_FORMAT.

If increasing the month by the specified number of months produces an invalid date, then the result is calculated to be the last day of the month, as shown in the last example below. When necessary, leap years are taken into account. For example, adding one month to 31-Jan-2012 will result in 29-Feb-2012.

Examples

ADD_MONTHS(12, '06-25-11') adds one year (12 months) to the specified date and returns the date 06-25-12.

ADD_MONTHS(3, '2011-06-25', DT_DELIM_Y_M_D) adds three months to the specified date and returns the date 2011-09-25.

ADD_MONTHS(1, '02-28-2011') returns the date 03-28-2011.

ADD_MONTHS(1, '03-31-2012') returns the date 04-30-2012.

Sample expressions and where its executed. DATE1, DATE2 are DB columns.

S.No Expression Execution On
1 DATE1 < ADD_MONTHS(1,DATE2) Database
2 DATE1 < ADD_MONTHS(1,DATE2,DELIM_M_D_Y,DB) Database
3 ADD_MONTHS(1,'02-29-2016',DELIM_M_D_Y,DB) > DATE1 Database
4 ADD_MONTHS(24,'2012-02-29',DT_DELIM_Y_M_D) > DATE2 Campaign Server
5 ADD_MONTHS(24,'2012-02-29',DT_DELIM_Y_M_D,DB) < DATE2 Database
6 DATE2 < ADD_MONTHS(1, DATE2) Campaign Server
7 DATE2 < ADD_MONTHS(1, DATE2,DELIM_M_D_Y,DB) Database
8 ADD_MONTHS(24,'2012-02-29', DELIM_Y_M_D, DB) > DATE2 Database
9 ADD_MONTHS(24,'02-29-2020') > DATE2 Campaign Server
10 DATE1 = ADD_MONTHS(1,DATE2) Database
11 DATE1 = ADD_MONTHS(1,DATE2,DELIM_M_D_Y,DB) Database
12 DATE1 != ADD_MONTHS(1,DATE2,DELIM_M_D_Y,DB) Database
13 DATE1 != ADD_MONTHS(1,DATE2) Database
14 ADD_MONTHS(3,'11NOV',DDMMM,DB) >DATE_FORMAT( DATE1,DT_DELIM_Y_M_D,DDMMM) Campaign Server
15 ADD_MONTHS(0,'2012-02-29',DT_DELIM_Y_M_D) < DATE1 Database
16 ADD_MONTHS(-1, DATE1, DT_DELIM_Y_M_D, DB) < DATE2 Database

Related functions

Function Description
DATE Converts a date string into a Julian date.
DATE_FORMAT Transforms a date of input_format to output_format.