SET_DATE_PART
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 following expression changes the year to 2001 for all dates in the SHIP_DATE port:
SET_DATE_PART( SHIP_DATE, 'YY', 2001 )
- •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 following expression changes the month to October for all dates in the SHIP_DATE port:
SET_DATE_PART( SHIP_DATE, 'MONTH', 10 )
- •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 following expression changes the day to 10 for all dates in the SHIP_DATE port:
SET_DATE_PART( SHIP_DATE, 'DD', 10 )
- •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 following expression changes the hour to 14:00:00 (or 2:00:00PM) for all dates in the SHIP_DATE port:
SET_DATE_PART( SHIP_DATE, 'HH', 14 )
- •Minute. Change the minutes by entering a positive integer between 0 and 59 in the value argument. Use the MI format string to set the minute. For example, the following expression changes the minute to 25 for all dates in the SHIP_DATE port:
SET_DATE_PART( SHIP_DATE, 'MI', 25 )
- •Seconds. Change the seconds by entering a positive integer between 0 and 59 in the value argument. Use the SS format string to set the second. For example, the following expression changes the second to 59 for all dates in the SHIP_DATE port:
SET_DATE_PART( SHIP_DATE, 'SS', 59 )
- •Milliseconds. Change the milliseconds by entering a positive integer between 0 and 999 in the value argument. Use the MS format string to set the milliseconds. For example, the following expression changes the milliseconds to 125 for all dates in the SHIP_DATE port:
SET_DATE_PART( SHIP_DATE, 'MS', 125 )
- •Microseconds. Change the microseconds by entering a positive integer between 1000 and 999999 in the value argument. Use the US format string to set the microseconds. For example, the following expression changes the microseconds to 12555 for all dates in the SHIP_DATE port:
SET_DATE_PART( SHIP_DATE, 'US', 12555 )
- •Nanoseconds. Change the nanoseconds by entering a positive integer between 1000000 and 999999999 in the value argument. Use the NS format string to set the nanoseconds. For example, the following expression changes the nanoseconds to 12555555 for all dates in the SHIP_DATE port:
SET_DATE_PART( SHIP_DATE, 'NS', 12555555 )
Syntax
SET_DATE_PART( date, format, value )
The following table describes the arguments for this command:
Argument | Required/ Optional | Description |
|---|
date | Required | Date/Time datatype. The date you want to modify. You can enter any valid transformation expression. |
format | Required | 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 such as 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.
Examples
The following expressions change the hour to 4PM for each date in the DATE_PROMISED port:
SET_DATE_PART( DATE_PROMISED, 'HH', 16 )
SET_DATE_PART( DATE_PROMISED, 'HH12', 16 )
SET_DATE_PART( DATE_PROMISED, 'HH24', 16 )
DATE_PROMISED | RETURN VALUE |
|---|
Jan 1 1997 12:15:56AM | Jan 1 1997 4:15:56PM |
Feb 13 1997 2:30:01AM | Feb 13 1997 4:30:01PM |
Mar 31 1997 5:10:15PM | Mar 31 1997 4:10:15PM |
Dec 12 1997 8:07:33AM | Dec 12 1997 4:07:33PM |
NULL | NULL |
The following expressions change the month to June for the dates in the DATE_PROMISED port. The Data Integration Service displays an error when you try to create a date that does not exist, such as changing March 31 to June 31:
SET_DATE_PART( DATE_PROMISED, 'MM', 6 )
SET_DATE_PART( DATE_PROMISED, 'MON', 6 )
SET_DATE_PART( DATE_PROMISED, 'MONTH', 6 )
DATE_PROMISED | RETURN VALUE |
|---|
Jan 1 1997 12:15:56AM | Jun 1 1997 12:15:56AM |
Feb 13 1997 2:30:01AM | Jun 13 1997 2:30:01AM |
Mar 31 1997 5:10:15PM | Error. Integration Service doesn't write row. |
Dec 12 1997 8:07:33AM | Jun 12 1997 8:07:33AM |
NULL | NULL |
The following expressions change the year to 2000 for the dates in the DATE_PROMISED port:
SET_DATE_PART( DATE_PROMISED, 'Y', 2000 )
SET_DATE_PART( DATE_PROMISED, 'YY', 2000 )
SET_DATE_PART( DATE_PROMISED, 'YYY', 2000 )
SET_DATE_PART( DATE_PROMISED, 'YYYY', 2000 )
DATE_PROMISED | RETURN VALUE |
|---|
Jan 1 1997 12:15:56AM | Jan 1 2000 12:15:56AM |
Feb 13 1997 2:30:01AM | Feb 13 2000 2:30:01AM |
Mar 31 1997 5:10:15PM | Mar 31 2000 5:10:15PM |
Dec 12 1997 8:07:33AM | Dec 12 2000 4:07:33PM |
NULL | NULL |
Tip
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 port to July 1 1998:
SET_DATE_PART( SET_DATE_PART( SET_DATE_PART( DATE_ENTERED, 'YYYY', 1998),MM', 7), 'DD', 1)