Sets one part of a date/time value to a value you specify.
With SET_DATE_PART, you can change the following parts of a date:
Year
Change the year by entering a positive integer in the value argument. Use any of the year format strings: Y, YY, YYY, or YYYY to set the year. For example, the expression SET_DATE_PART( SHIP_DATE, 'YY', 2001 ) changes the year to 2001 for all dates in the SHIP_DATE column.
Month
Change the month by entering a positive integer between 1 and 12 (January=1 and December=12) in the value argument. Use any of the month format strings: MM, MON, MONTH to set the month. For example, the expression SET_DATE_PART( SHIP_DATE, 'MONTH', 10 ) changes the month to October for all dates in the SHIP_DATE column.
Day
Change the day by entering a positive integer between 1 and 31 (except for the months that have less than 31 days: February, April, June, September, and November) in the value argument. Use any of the month format strings (D, DD, DDD, DY, and DAY) to set the day. For example, the expression SET_DATE_PART( SHIP_DATE, 'DD', 10 ) changes the day to 10 for all dates in the SHIP_DATE column.
Hour
Change the hour by entering a positive integer between 0 and 24 (where 0=12AM, 12=12PM, and 24 =12AM) in the value argument. Use any of the hour format strings (HH, HH12, HH24) to set the hour. For example, the expression SET_DATE_PART( SHIP_DATE, 'HH', 14 ) changes the hour to 14:00:00 (or 2:00:00PM) for all dates in the SHIP_DATE column.
Minute
Change the minutes by entering a positive integer between 0 and 59 in the value argument. You use the MI format string to set the minute. For example, the expression SET_DATE_PART( SHIP_DATE, 'MI', 25 ) changes the minute to 25 for all dates in the SHIP_DATE column.
Seconds
You can change the seconds by entering a positive integer between 0 and 59 in the value argument. You use the SS format string to set the second. For example, the expression SET_DATE_PART( SHIP_DATE, 'SS', 59 ) changes the second to 59 for all dates in the SHIP_DATE column.
Syntax
SET_DATE_PART( date, format, value )
Argument
Required/
Optional
Description
date
Required
Date/Time datatype. The date you want to modify. You can enter any valid expression.
format
Required
A format string specifying the portion of the date to be changed. The format string is not case sensitive.
value
Required
A positive integer value assigned to the specified portion of the date. The integer must be a valid value for the part of the date you want to change. If you enter an improper value (for example, February 30), the session fails.
Return Value
Date in the same format as the source date with the specified part changed.
NULL if a value passed to the function is NULL.
Example
The following expressions change the hour to 4PM for each date in the DATE_PROMISED column:
The following expressions change the month to June for the dates in the DATE_PROMISED column. Data Integration displays an error when you try to create a date that does not exist, such as changing March 31 to June 31:
If you want to change multiple parts of a date at one time, you can nest multiple SET_DATE_PART functions within the date argument. For example, you might write the following expression to change all of the dates in the DATE_ENTERED column to July 1 1998: