Design

Back Next

addToDate

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. The addToDate function accepts positive and negative integer values. Use addToDate to change the following parts of a date:

Year.
Enter a positive or negative integer in the amount
argument. Use any of the year format strings: Y, YY, YYY, or YYYY. The following expression adds 10 years to all dates in the SHIP_DATE column:

date:addToDate(xs:dateTime('SHIP_DATE'), 'YY', 10)

Month.
Enter a positive or negative integer in the amount
argument. Use any of the month format strings: MM, MON, MONTH. The following expression subtracts 10 months from each date in the SHIP_DATE column:

date:addToDate(xs:dateTime('SHIP_DATE'), 'MONTH', -10)

Day.
Enter a positive or negative integer in the amount
argument. Use any of the day format strings: D, DD, DDD, DY, and DAY. The following expression adds 10 days to each date in the SHIP_DATE column:

date:addToDate(xs:dateTime('SHIP_DATE'), 'DD', 10)

Hour.
Enter a positive or negative integer in the amount
argument. Use any of the hour format strings: HH, HH12, HH24. The following expression adds 14 hours to each date in the SHIP_DATE column:

date:addToDate(xs:dateTime('SHIP_DATE'), 'HH', 14)

Minute.
Enter a positive or negative integer in the amount
argument. Use the MI format string to set the minute. The following expression adds 25 minutes to each date in the SHIP_DATE column:

date:addToDate(xs:dateTime('SHIP_DATE'), 'MI', 25)

Seconds.
Enter a positive or negative integer in the amount
argument. Use the SS format string to set the second. The following expression adds 59 seconds to each date in the SHIP_DATE column:

date:addToDate(xs:dateTime('SHIP_DATE'), 'SS', 59)

Milliseconds.
Enter a positive or negative integer in the amount
argument. Use the MS format string to set the milliseconds. The following expression adds 125 milliseconds to each date in the SHIP_DATE column:

date:addToDate(xs:dateTime('SHIP_DATE'), 'MS', 125)

Microseconds.
Enter a positive or negative integer in the amount
argument. Use the US format string to set the microseconds. The following expression adds 2,000 microseconds to each date in the SHIP_DATE column:

date:addToDate(xs:dateTime('SHIP_DATE'), 'US', 2000)

Syntax

date:addToDate(xs:dateTime('date'), 'format', amount)

You must manually add the xs:dateTime phrase and enclose the date values within single quotation marks.

The following table describes the arguments:

Argument	Required/ Optional	Description
date	Required	Date/Time data type. Passes the values that you want to change. You can enter any valid transformation expression.
format	Required	A format string that specifies the portion of the date value that 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 that specifies the amount of years, months, days, hours, and so on by which you want to change the date value. You can enter any valid transformation 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.

Examples

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, addToDate returns the last day of the month. For example, if you add one month to Jan 31 1998, addToDate returns Feb 28 1998.

Also, addToDate recognizes leap years and adds one month to Jan 29 2000:

date:addToDate(xs:dateTime('DATE_SHIPPED'), 'MM', 1)
date:addToDate(xs:dateTime('DATE_SHIPPED'), 'MON', 1)
date:addToDate(xs:dateTime('DATE_SHIPPED'), 'MONTH', 1)

The following table shows some sample values and return values:

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:

date:addToDate(xs:dateTime('DATE_SHIPPED'), 'D', -10)
date:addToDate(xs:dateTime('DATE_SHIPPED'), 'DD', -10)
date:addToDate(xs:dateTime('DATE_SHIPPED'), 'DDD', -10)
date:addToDate(xs:dateTime('DATE_SHIPPED'), 'DY', -10)
date:addToDate(xs:dateTime('DATE_SHIPPED'), 'DAY', -10)

The following table shows some sample values and return values:

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:

date:addToDate(xs:dateTime('DATE_SHIPPED'), 'HH', -15)
date:addToDate(xs:dateTime('DATE_SHIPPED'), 'HH12', -15)
date:addToDate(xs:dateTime('DATE_SHIPPED'), 'HH24', -15)

The following table shows some sample values and return values:

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