EXTRACT
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-.ISOWEEKs begin on Monday. Returns values in the range [1, 53]. The firstISOWEEKof 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.
