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:
- •Year. Enter a positive or negative integer in the amount argument. Use any of the year format strings: Y, YY, YYY, or YYYY. For example, the expression ADD_TO_DATE ( SHIP_DATE, 'YY', 10 ) adds 10 years to all dates in the SHIP_DATE column.
- •Month. Enter a positive or negative integer in the amount argument. Use any of the month format strings: MM, MON, MONTH. For example, the expression ADD_TO_DATE( SHIP_DATE, 'MONTH', -10 ) subtracts 10 months from each date in the SHIP_DATE column.
- •Day. Enter a positive or negative integer in the amount argument. Use any of the day format strings: D, DD, DDD, DY, and DAY. For example, the expression ADD_TO_DATE( SHIP_DATE, 'DD', 10 ) adds 10 days to each date in the SHIP_DATE column.
- •Hour. Enter a positive or negative integer in the amount argument. Use any of the hour format strings: HH, HH12, HH24. For example, the expression ADD_TO_DATE( SHIP_DATE, 'HH', 14 ) adds 14 hours to each date in the SHIP_DATE column.
- •Minute. Enter a positive or negative integer in the amount argument. Use the MI format string to set the minute. For example, the expression ADD_TO_DATE( SHIP_DATE, 'MI', 25 ) adds 25 minutes to each date in the SHIP_DATE column.
- •Seconds. Enter a positive or negative integer in the amount argument. Use the SS format string to set the second. For example, the following expression adds 59 seconds to each date in the SHIP_DATE column:
ADD_TO_DATE( SHIP_DATE, 'SS', 59 )
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, Informatica Cloud returns the last day of the month. For example, if you add one month to Jan 31 1998, Informatica Cloud 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:
- •You can add or subtract any part of the date by specifying a format string and making the amount argument a positive or negative integer.
- •If you pass a value that creates a day that does not exist in a particular month, Informatica Cloud returns the last day of the month. For example, if you add one month to Jan 31 1998, Informatica Cloud returns Feb 28 1998.
- •You can nest TRUNC and ROUND to manipulate dates.
- •You can nest TO_DATE to convert strings to dates.
- •ADD_TO_DATE changes only one portion of the date, which you specify. If you modify a date so that it changes from standard to daylight savings time, you need to change the hour portion of the date.