Function Reference > Functions > ADD_TO_DATE
  

ADD_TO_DATE

Adds a specified amount to one part of a datetime value, and returns a date in the same format as the date you pass to the function.
ADD_TO_DATE accepts positive and negative integer values. Use ADD_TO_DATE to change the following parts of a date:

Syntax

ADD_TO_DATE( date, format, amount )
Argument
Required/
Optional
Description
date
Required
Date/Time datatype. Passes the values you want to change. You can enter any valid expression.
format
Required
A format string specifying the portion of the date value you want to change. Enclose the format string within single quotation marks, for example, 'mm'. The format string is not case sensitive.
amount
Required
An integer value specifying the amount of years, months, days, hours, and so on by which you want to change the date value. You can enter any valid expression that evaluates to an integer.

Return Value

Date in the same format as the date you pass to this function.
NULL if a null value is passed as an argument to the function.

Example

The following expressions all add one month to each date in the DATE_SHIPPED column. If you pass a value that creates a day that does not exist in a particular month, Data Integration returns the last day of the month. For example, if you add one month to Jan 31 1998, Data Integration returns Feb 28 1998.
ADD_TO_DATE recognizes leap years and adds one month to Jan 29 2000:
ADD_TO_DATE( DATE_SHIPPED, 'MM', 1 )
ADD_TO_DATE( DATE_SHIPPED, 'MON', 1 )
ADD_TO_DATE( DATE_SHIPPED, 'MONTH', 1 )
DATE_SHIPPED
RETURN VALUE
Jan 12 1998 12:00:30AM
Feb 12 1998 12:00:30AM
Jan 31 1998 6:24:45PM
Feb 28 1998 6:24:45PM
Jan 29 2000 5:32:12AM
Feb 29 2000 5:32:12AM  (Leap Year)
Oct 9 1998 2:30:12PM
Nov 9 1998 2:30:12PM
NULL
NULL
The following expressions subtract 10 days from each date in the DATE_SHIPPED column:
ADD_TO_DATE( DATE_SHIPPED, 'D', -10 )
ADD_TO_DATE( DATE_SHIPPED, 'DD', -10 )
ADD_TO_DATE( DATE_SHIPPED, 'DDD', -10 )
ADD_TO_DATE( DATE_SHIPPED, 'DY', -10 )
ADD_TO_DATE( DATE_SHIPPED, 'DAY', -10 )
DATE_SHIPPED
RETURN VALUE
Jan 1 1997 12:00:30AM
Dec 22 1996 12:00AM
Jan 31 1997 6:24:45PM
Jan 21 1997 6:24:45PM
Mar 9 1996 5:32:12AM
Feb 29 1996 5:32:12AM  (Leap Year)
Oct 9 1997 2:30:12PM
Sep 30 1997 2:30:12PM
Mar 3 1996 5:12:20AM
Feb 22 1996 5:12:20AM
NULL
NULL
The following expressions subtract 15 hours from each date in the DATE_SHIPPED column:
ADD_TO_DATE( DATE_SHIPPED, 'HH', -15 )
ADD_TO_DATE( DATE_SHIPPED, 'HH12', -15 )
ADD_TO_DATE( DATE_SHIPPED, 'HH24', -15 )
DATE_SHIPPED
RETURN VALUE
Jan 1 1997 12:00:30AM
Dec 31 1996 9:00:30AM
Jan 31 1997 6:24:45PM
Jan 31 1997 3:24:45AM
Oct 9 1997 2:30:12PM
Oct 8 1997 11:30:12PM
Mar 3 1996 5:12:20AM
Mar 2 1996 2:12:20PM
Mar 1 1996 5:32:12AM
Feb 29 1996 2:32:12PM  (Leap Year)
NULL
NULL

Working with Dates

Use the following tips when working with ADD_TO_DATE: