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 the date_expression -. ISOWEEK s 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.

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.