Preface
The Transformation Language
Constants
Operators
Variables
- Built-in Variables
  - SYSDATE
    - Example
- Local Variables
Dates
Functions
- Function Categories
  - Aggregate Functions
  - Aggregate Functions and Nulls
    - Filter Conditions
  - Character Functions
  - Complex Functions
  - Conversion Functions
  - Data Cleansing Functions
  - Date Functions
  - Encoding Functions
  - Financial Functions
  - Numeric Functions
  - Scientific Functions
  - Special Functions
  - String Functions
  - Test Functions
  - Window Functions
- ABORT
- ABS
- ADD_TO_DATE
- AES_DECRYPT
- AES_ENCRYPT
- ANY
- ARRAY
- ASCII
- AVG
- CAST
- CEIL
- CHOOSE
- CHR
- CHRCODE
- COLLECT_LIST
- COLLECT_MAP
- COMPRESS
- CONCAT
- CONCAT_ARRAY
- CONVERT_BASE
- COS
- COSH
- COUNT
- CRC32
- CREATE_TIMESTAMP_TZ
- CUME
- DATE_COMPARE
- DATE_DIFF
- DEC_BASE64
- DECODE
- DECOMPRESS
- ENC_BASE64
- ERROR
- EXP
- EXTRACT_STRUCT
- FIRST
- FLOOR
- FV
- GET_DATE_PART
- GET_TIMEZONE
- GET_TIMESTAMP
- GREATEST
- IIF
- IN
- INDEXOF
- INITCAP
- INSTR
- ISNULL
- IS_DATE
- IS_NUMBER
- IS_SPACES
- LAG
- LAST
- LAST_DAY
- LEAD
- LEAST
- LENGTH
- LN
- LOG
- LOWER
- LPAD
- LTRIM
- MAKE_DATE_TIME
- MAP
- MAP_FROM_ARRAYS
- MAP_KEYS
- MAP_VALUES
- MAX (Dates)
- MAX (Numbers)
- MAX (String)
- MD5
- MEDIAN
- METAPHONE
- MIN (Dates)
- MIN (Numbers)
- MIN (String)
- MOD
- MOVINGAVG
- MOVINGSUM
- NPER
- PARSE_JSON
- PARSE_XML
- PERCENTILE
- PMT
- POWER
- PV
- RAND
- RATE
- REG_EXTRACT
- REG_MATCH
- REG_REPLACE
- REPLACECHR
- REPLACESTR
- RESPEC
- REVERSE
- ROUND (Dates)
- ROUND (Numbers)
- RPAD
- RTRIM
- SET_DATE_PART
- SIGN
- SIN
- SINH
- SIZE
- SOUNDEX
- SQL_LIKE
- SQRT
- STDDEV
- STRUCT
- STRUCT_AS
- SUBSTR
- SUM
- SYSTIMESTAMP
- TAN
- TANH
- TIME_RANGE
- TO_BIGINT
- TO_CHAR (Dates)
- TO_CHAR (Numbers)
- TO_DATE
- TO_DECIMAL
- TO_DECIMAL38
- TO_FLOAT
- TO_INTEGER
- TO_TIMESTAMP_TZ
- TRUNC (Dates)
- TRUNC (Numbers)
- UPPER
- UUID4
- UUID_UNPARSE
- VARIANCE

Transformation Language Reference

10.5.1
- 10.5.6
- 10.5.4
- 10.5
- 10.4.0

Back Next

GET_DATE_PART

Returns the specified part of a date as an integer value. Therefore, if you create an expression that returns the month portion of the date, and pass a date such as Apr 1 1997 00:00:00, GET_DATE_PART returns 4.

Syntax

GET_DATE_PART( date, format )

The following table describes the arguments for this command:

Argument	Required/ Optional	Description
date	Required	Date/Time datatype. You can enter any valid transformation expression.
format	Required	A format string specifying the portion of the date value you want to return. Enclose format strings within single quotation marks, for example, 'mm'. The format string is not case sensitive. Each format string returns the entire part of the date based on the date format specified in the mapping. For example, if you pass the date Apr 1 1997 to GET_DATE_PART, the format strings 'Y', 'YY', 'YYY', or 'YYYY' all return 1997.

Return Value

Integer representing the specified part of the date.

NULL if a value passed to the function is NULL.

Examples

The following expressions return the hour for each date in the DATE_SHIPPED port. 12:00:00AM returns 0 because the default date format is based on the 24 hour interval:

GET_DATE_PART( DATE_SHIPPED, 'HH' )
GET_DATE_PART( DATE_SHIPPED, 'HH12' )
GET_DATE_PART( DATE_SHIPPED, 'HH24' )

DATE_SHIPPED	RETURN VALUE
Mar 13 1997 12:00:00AM	0
Sep 2 1997 2:00:01AM	2
Aug 22 1997 12:00:00PM	12
June 3 1997 11:30:44PM	23
NULL	NULL

The following expressions return the day for each date in the DATE_SHIPPED port:

GET_DATE_PART( DATE_SHIPPED, 'D' )
GET_DATE_PART( DATE_SHIPPED, 'DD' )
GET_DATE_PART( DATE_SHIPPED, 'DDD' )
GET_DATE_PART( DATE_SHIPPED, 'DY' )
GET_DATE_PART( DATE_SHIPPED, 'DAY' )

DATE_SHIPPED	RETURN VALUE
Mar 13 1997 12:00:00AM	13
June 3 1997 11:30:44PM	3
Aug 22 1997 12:00:00PM	22
NULL	NULL

The following expressions return the month for each date in the DATE_SHIPPED port:

GET_DATE_PART( DATE_SHIPPED, 'MM' )
GET_DATE_PART( DATE_SHIPPED, 'MON' )
GET_DATE_PART( DATE_SHIPPED, 'MONTH' )

DATE_SHIPPED	RETURN VALUE
Mar 13 1997 12:00:00AM	3
June 3 1997 11:30:44PM	6
NULL	NULL

The following expression return the year for each date in the DATE_SHIPPED port:

GET_DATE_PART( DATE_SHIPPED, 'Y' )
GET_DATE_PART( DATE_SHIPPED, 'YY' )
GET_DATE_PART( DATE_SHIPPED, 'YYY' )
GET_DATE_PART( DATE_SHIPPED, 'YYYY' )

DATE_SHIPPED	RETURN VALUE
Mar 13 1997 12:00:00AM	1997
June 3 1997 11:30:44PM	1997
NULL	NULL

Rename Saved Search

Table of Contents

Transformation Language Reference

Transformation Language Reference

GET_DATE_PART

GET_DATE_PART

Syntax

Return Value

Examples