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 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:
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 column. Informatica Cloud 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 | None. Informatica Cloud writes the row into the error rows file. |
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 column:
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 column to July 1 1998:
SET_DATE_PART( SET_DATE_PART( SET_DATE_PART( DATE_ENTERED, 'YYYY', 1998),MM', 7), 'DD', 1)