Table of Contents

Search

  1. Preface
  2. The Transformation Language
  3. Constants
  4. Operators
  5. Variables
  6. Dates
  7. Functions

Transformation Language Reference

Transformation Language Reference

SET_DATE_PART

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)

0 COMMENTS

We’d like to hear from you!