EXTRACT

Description

EXTRACT

is a row function that returns the specified portion of a

DATE

value.

Syntax

EXTRACT

("

extract_value

",

DATE

)

Return Value

Returns the specified extracted value as type

INTEGER

.

EXTRACT

removes leading zeros. For example, the month of April returns a value of

4

, not

04

.

Input Parameters

extract_value: Required. One of the following extract values:
millisecond

- Returns the millisecond portion of a date value. For example, an input date value of
2012-08-15 20:38:40.213
would return an integer value of
213
.
second

- Returns the second portion of a date value. For example, an input date value of
2012-08-15 20:38:40.213
would return an integer value of
40
.
minute

- Returns the minute portion of a date value. For example, an input date value of
2012-08-15 20:38:40.213
would return an integer value of
38
.
hour

- Returns the hour portion of a date value. For example, an input date value of
2012-08-15 20:38:40.213
would return an integer value of
20
.
day

- Returns the day portion of a date value. For example, an input date value of
2012-08-15
would return an integer value of
15

.
week

- Returns the ISO week number for the input date value. For example, an input date value of
2012-01-02
would return an integer value of
1
(the first ISO week of 2012 starts on Monday January 2). An input date value of
2012-01-01
would return an integer value of
52
(January 1, 2012 is part of the last ISO week of 2011).
month

- Returns the month portion of a date value. For example, an input date value of
2012-08-15
would return an integer value of
8
.
quarter

- Returns the quarter number for the input date value, where quarters start on January 1, April 1, July 1, or October 1. For example, an input date value of
2012-08-15
would return a integer value of
3
.
year

- Returns the year portion of a date value. For example, an input date value of
2012-01-01
would return an integer value of
2012
.
weekyear

- Returns the year value that corresponds to the ISO week number of the input date value. For example, an input date value of
2012-01-02
would return an integer value of
2012
(the first ISO week of 2012 starts on Monday January 2). An input date value of
2012-01-01
would return an integer value of
2011
(January 1, 2012 is part of the last ISO week of 2011).
date: Required. A field name or expression that returns a
DATE
value.

Examples

Extract the hour portion from the order_date
Date field:

EXTRACT("hour",[order_date])

Cast the value of the order_date

Text

field to a date value using

TO_DATE

, and extract the ISO week year:

EXTRACT("weekyear",TO_DATE([order_date],"MM/dd/yyyy HH:mm:ss"))