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