GET_DATE_PART
Returns the specified part of a date as an integer value. For example, 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 )
Argument | Required/ Optional | Description |
|---|
date | Required | Date/Time datatype. You can enter any valid expression. |
format | Required | A format string specifying the portion of the date value you want to change. 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 default format of MM/DD/YYYY HH24:MI:SS. 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.
Example
The following expressions return the hour for each date in the DATE_SHIPPED column. 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 column:
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 column:
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 column:
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 |