EXTRACT function

The EXTRACT function extracts fields (or calculates values) from datetime values and intervals.

The result is INTEGER for YEAR, MONTH, DAY, HOUR, MINUTE, DAYS, DAYOFYEAR, DAYOFWEEK, MONTHS, QUARTEROFYEAR, QUARTERS, WEEKS, WEEKOFYEAR, and WEEKOFMONTH extracts, but FLOAT for SECOND extracts, and BOOLEAN for ISLEAPYEAR extracts. If the SourceDate is NULL, the result is NULL regardless of the type of extract.

Syntax

Read syntax diagramSkip visual syntax diagramEXTRACT( YEARMONTHDAYHOURMINUTESECONDDAYSDAYOFYEARDAYOFWEEKMONTHSQUARTEROFYEARQUARTERSWEEKSWEEKOFYEARWEEKOFMONTHISLEAPYEAR FROM SourceDate )

EXTRACT extracts individual fields from datetime values and intervals. You can extract a field only if it is present in the datetime value specified in the second parameter. Either a parse-time or a runtime error is generated if the requested field does not exist within the data type.

The following table describes the extracts that are supported:
Note: All new integer values start from 1.
Table 1.
Extract Description
YEAR Year
MONTH Month
DAY Day
HOUR Hour
MINUTE Minute
SECOND Second
DAYS Days encountered between 1st January 0001 and the SourceDate.
DAYOFYEAR Day of year
DAYOFWEEK Day of the week: Sunday = 1, Monday = 2, Tuesday = 3, Wednesday = 4, Thursday = 5, Friday = 6, Saturday = 7.
MONTHS Months encountered between 1st January 0001 and the SourceDate.
QUARTEROFYEAR Quarter of year: January to March = 1, April to June = 2, July to September = 3, October to December = 4.
QUARTERS Quarters encountered between 1st January 0001 and the SourceDate.
WEEKS Weeks encountered between 1st January 0001 and the SourceDate.
WEEKOFYEAR Week of year
WEEKOFMONTH Week of month
ISLEAPYEAR Whether this is a leap year
Notes:
  1. A week is defined as Sunday to Saturday, not any seven consecutive days. You must convert to an alternative representation scheme if required.
  2. The source date time epoch is 1 January 0001. Dates before the epoch are not valid for this function.
  3. The Gregorian calendar is assumed for calculation.

Example

EXTRACT(YEAR FROM CURRENT_DATE)
and
EXTRACT(HOUR FROM LOCAL_TIMEZONE)
both work without error, but 
EXTRACT(DAY FROM CURRENT_TIME)
fails.
EXTRACT (DAYS FROM DATE '2000-02-29')
calculates the number of days encountered since year 1 to '2000-02-29' and
EXTRACT (DAYOFYEAR FROM CURRENT_DATE)
calculates the number of days encountered since the beginning of the current year but
EXTRACT (DAYOFYEAR FROM CURRENT_TIME)
fails because CURRENT_TIME does not contain date information.