Returns part of a date.
Sample usage
EXTRACT(QUARTER FROM Order Date)
Syntax
1) Returns a date part.
EXTRACT(part FROM date_expression)
2) Returns a Date from a Date & Time field or expression.
EXTRACT(DATE FROM date_expression)
Parameters
date_expression
- a Date or a Date & Time field or expression.
part
- the date part to return. EXTRACT
supports the following parts:
DAYOFWEEK
: Returns values in the range [1, 7] with Sunday as the first day of the week.DAY
DAYOFYEAR
WEEK
: Returns the week number of the date in the range [0, 53]. Weeks begin with Sunday, and dates prior to the first Sunday of the year are in week 0.ISOWEEK
: Returns the ISO 8601 week number of thedate_expression
- .ISOWEEK
s begin on Monday. Returns values in the range [1, 53]. The firstISOWEEK
of each ISO year begins on the Monday before the first Thursday of the Gregorian calendar year.MONTH
QUARTER
: Returns values in the range [1, 4].YEAR
ISOYEAR
: Returns the ISO 8601 week-numbering year, which is the Gregorian calendar year containing the Thursday of the week to whichdate_expression
- belongs.
Return data types
- Number (integer)
- Date
Examples
In the following example, EXTRACT
returns a value corresponding to the DAY
time part.
Example formula | Output |
EXTRACT(DAY FROM DATE '2013-12-25') |
25 |
In the following example, EXTRACT
returns values corresponding to different time parts from a column of dates near the end of the year.
Field name | Example formula |
---|---|
Isoyear | EXTRACT(ISOYEAR FROM Date) |
Isoweek | EXTRACT(ISOWEEK FROM Date) |
Year | EXTRACT(YEAR FROM Date) |
Week | EXTRACT(WEEK FROM Date) |
Output:
Input date | Isoyear | Isoweek | Year | Week |
---|---|---|---|---|
2015-12-23 | 2015 | 52 | 2015 | 51 |
2015-12-24 | 2015 | 52 | 2015 | 51 |
2015-12-25 | 2015 | 52 | 2015 | 51 |
2015-12-26 | 2015 | 52 | 2015 | 51 |
2015-12-27 | 2015 | 52 | 2015 | 52 |
2015-12-28 | 2015 | 53 | 2015 | 52 |
2015-12-29 | 2015 | 53 | 2015 | 52 |
2015-12-30 | 2015 | 53 | 2015 | 52 |
2015-12-31 | 2015 | 53 | 2015 | 52 |
2016-01-01 | 2015 | 53 | 2016 | 0 |
2016-01-02 | 2015 | 53 | 2016 | 0 |
2016-01-03 | 2015 | 53 | 2016 | 1 |
2016-01-04 | 2016 | 1 | 2016 | 1 |
2016-01-05 | 2016 | 1 | 2016 | 1 |
2016-01-06 | 2016 | 1 | 2016 | 1 |
2016-01-07 | 2016 | 1 | 2016 | 1 |
2016-01-08 | 2016 | 1 | 2016 | 1 |
2016-01-09 | 2016 | 1 | 2016 | 1 |
Notes
This function is not available for compatibility mode date types.