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 datetime_expression)

Parameters

date_expression - a Date or a Date & Time field or expression.

datetime_expression - 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 the date_expression. ISOWEEKs begin on Monday. Returns values in the range [1, 53]. The first ISOWEEK 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 which date_expression belongs.

Return data types

  1. Number (integer)
  2. Date

Examples

In the following example, EXTRACT returns a value corresponding to the DAY time part.

Formula Result
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 Formula
Isoyear EXTRACT(ISOYEAR FROM Date)
Isoweek EXTRACT(ISOWEEK FROM Date)
Year EXTRACT(YEAR FROM Date)
Week EXTRACT(WEEK FROM Date)

Output:

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.

 

Related resources

Was this helpful?
How can we improve it?

Need more help?

Sign in for additional support options to quickly solve your issue