Communities
A collaborative platform to connect and grow with like-minded Informaticans across the globe
Product Communities
Connect and collaborate with Informatica experts and champions
Discussions
Have a question? Start a Discussion and get immediate answers you are looking for
User Groups
Customer-organized groups that meet online and in-person. Join today to network, share ideas, and get tips on how to get the most out of Informatica
Get Started
Community Guidelines
Knowledge Center
Troubleshooting documents, product guides, how to videos, best practices, and more
Knowledge Base
One-stop self-service portal for solutions, FAQs, Whitepapers, How Tos, Videos, and more
Support TV
Video channel for step-by-step instructions to use our products, best practices, troubleshooting tips, and much more
Documentation
Information library of the latest product documents
Velocity (Best Practices)
Best practices and use cases from the Implementation team
Learn
Rich resources to help you leverage full capabilities of our products
Trainings
Role-based training programs for the best ROI
Certifications
Get certified on Informatica products. Free, Foundation, or Professional
Product Learning Paths
Free and unlimited modules based on your expertise level and journey
Resources
Library of content to help you leverage the best of Informatica products
Tech Tuesdays Webinars
Most popular webinars on product architecture, best practices, and more
Product Availability Matrix
Product Availability Matrix statements of Informatica products
SupportFlash
Monthly support newsletter
Support Documents
Informatica Support Guide and Statements, Quick Start Guides, and Cloud Product Description Schedule
Product Lifecycle
End of Life statements of Informatica products
Ideas
Events
Change Request Tracking
Marketplace
English
  • PowerCenter 10.5.1
  • All Products

Table of Contents

Search

  1. Preface
  2. The Transformation Language
  3. Constants
  4. Operators
  5. Variables
  6. Dates
  7. Functions
  8. Creating Custom Functions
  9. Custom Function API Reference

Transformation Language Reference

Transformation Language Reference

Back Next

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
PowerCenter 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!