Preface
Function reference
- Function components
Constants
Operators
Dates
Functions
- Function overview
  - Aggregate functions
    - Filter conditions
  - Conversion functions
  - Data cleansing functions
  - Date functions
  - Encoding functions
  - Financial functions
  - Horizontal expansion functions
  - Numeric functions
  - Scientific functions
  - Special functions
  - String functions
  - Test functions
  - Window functions
    - Aggregate functions as window functions
    - Nested aggregate functions as window functions
- Function quick reference
- Functions in advanced mode
- %OPR_CONCAT%
- %OPR_CONCATDELIM%
- %OPR_IIF%
- %OPR_SUM%
- ABORT
- ABS
- ADD_TO_DATE
- AES_DECRYPT
- AES_ENCRYPT
- AES_GCM_DECRYPT
- AES_GCM_ENCRYPT
- ASCII
- AVG
- CEIL
- CHOOSE
- CHR
- CHRCODE
- COMPRESS
- CONCAT
- CONVERT_BASE
- COS
- COSH
- COUNT
- CRC32
- CUME
- DATE_COMPARE
- DATE_DIFF
- DEC_BASE64
- DECODE
- DECOMPRESS
- ENC_BASE64
- ERROR
- EXP
- FIRST
- FLOOR
- FV
- GET_DATE_PART
- GREATEST
- IIF
- IN
- INDEXOF
- INITCAP
- INSTR
- IS_DATE
- IS_NUMBER
- IS_SPACES
- ISNULL
- LAG
- LAST
- LAST_DAY
- LEAD
- LEAST
- LENGTH
- LN
- LOG
- LOWER
- LPAD
- LTRIM
- MAKE_DATE_TIME
- MAX (Dates)
- MAX (Numbers)
- MAX (String)
- MD5
- MEDIAN
- METAPHONE
- MIN (Dates)
- MIN (Numbers)
- MIN (String)
- MOD
- MOVINGAVG
- MOVINGSUM
- NPER
- PERCENTILE
- PMT
- POWER
- PV
- RAND
- RATE
- REG_EXTRACT
- REG_MATCH
- REG_REPLACE
- REPLACECHR
- REPLACESTR
- REVERSE
- ROUND (Dates)
- ROUND (Numbers)
- RPAD
- RTRIM
- SET_DATE_PART
- SETCOUNTVARIABLE
- SETMAXVARIABLE
- SETMINVARIABLE
- SETVARIABLE
- SHA256
- SIGN
- SIN
- SINH
- SOUNDEX
- SQRT
- STDDEV
- SUBSTR
- SUM
- SYSTIMESTAMP
- TAN
- TANH
- TO_BIGINT
- TO_CHAR (Dates)
- TO_CHAR (Numbers)
- TO_DATE
- TO_DECIMAL
- TO_FLOAT
- TO_INTEGER
- TRUNC (Dates)
- TRUNC (Numbers)
- UPPER
- VARIANCE
System variables
Datatype reference

Function Reference

Back Next

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.

Data Integration

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. Data Integration 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)

Functions

Download Guide

Watch

Comments

Cloud Data Integration Homepage

0 COMMENTS

We’d like to hear from you! Log in to comment.

Rename Saved Search

Table of Contents

Function Reference

Function Reference

SET_DATE_PART

SET_DATE_PART

Syntax

Return Value

Example

Tip