Notification

This Help Center is moving to Cloud. Learn more about the migration.

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 - . 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.

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.

Related resources

Was this helpful?

How can we improve it?
true
What's new in Looker Studio?

Learn about new features and recent changes. Looker Studio release notes live on Google Cloud.

Search
Clear search
Close search
Google apps
Main menu
32053817225842060
true
Search Help Center
true
true
true
true
true
102097
false
false