Categories:
Date & Time Functions

YEAR* / DAY* / WEEK* / MONTH / QUARTER

Extracts the corresponding date part from a date or timestamp.

These functions are alternatives to using the DATE_PART (or EXTRACT) function with the equivalent date part (see Supported Date and Time Parts).

See also:
HOUR / MINUTE / SECOND

Syntax

YEAR( <date_or_timestamp_expr> )

YEAROFWEEK( <date_or_timestamp_expr> )
YEAROFWEEKISO( <date_or_timestamp_expr> )

DAY( <date_or_timestamp_expr> )

DAYOFMONTH( <date_or_timestamp_expr> )
DAYOFWEEK( <date_or_timestamp_expr> )
DAYOFWEEKISO( <date_or_timestamp_expr> )
DAYOFYEAR( <date_or_timestamp_expr> )

WEEK( <date_or_timestamp_expr> )

WEEKOFYEAR( <date_or_timestamp_expr> )
WEEKISO( <date_or_timestamp_expr> )

MONTH( <date_or_timestamp_expr> )

QUARTER( <date_or_timestamp_expr> )

Usage Notes

Function Name Date Part Extracted from Input Date / Timestamp Possible Values
YEAR Year Any valid year (e.g. 2017)
YEAROFWEEK [1] Year that the extracted week belongs to Any valid year (e.g. 2017)
YEAROFWEEKISO Same as YEAROFWEEK, except uses ISO semantics Any valid year (e.g. 2017)
DAY , DAYOFMONTH Day (number) of the month 1 to 31
DAYOFWEEK [1] Day (number) of the week 0 to 7
DAYOFWEEKISO Same as DAYOFWEEK, except uses ISO semantics 1 to 7
DAYOFYEAR Day (number) of the year 1 to 366
WEEK , WEEKOFYEAR [1] Week (number) of the year 1 to 54
WEEKISO Same as WEEK, except uses ISO semantics 1 to 53
MONTH Month (number) of the year 1 to 12
QUARTER Quarter (number) of the year 1 to 4

[1] Results dictated by the values set for the WEEK_OF_YEAR_POLICY and/or WEEK_START session parameters.

For details about ISO semantics and the parameter, see Calendar Weeks and Weekdays.

Examples

The following demonstrates the use of the functions YEAR, MONTH, DAY, DAYOFWEEK, DAYOFYEAR, and QUARTER:

SELECT 
       '2013-05-08T23:39:20.123-07:00'::TIMESTAMP AS tstamp,
       YEAR(tstamp) AS "YEAR", 
       QUARTER(tstamp) AS "QUARTER OF YEAR",
       MONTH(tstamp) AS "MONTH", 
       DAY(tstamp) AS "DAY",
       DAYOFMONTH(tstamp) AS "DAY OF MONTH",
       DAYOFYEAR(tstamp) AS "DAY OF YEAR";
+-------------------------+------+-----------------+-------+-----+--------------+-------------+
| TSTAMP                  | YEAR | QUARTER OF YEAR | MONTH | DAY | DAY OF MONTH | DAY OF YEAR |
|-------------------------+------+-----------------+-------+-----+--------------+-------------|
| 2013-05-08 23:39:20.123 | 2013 |               2 |     5 |   8 |            8 |         128 |
+-------------------------+------+-----------------+-------+-----+--------------+-------------+

The following demonstrates the use of the functions WEEK, WEEKISO, WEEKOFYEAR, YEAROFWEEK, and YEAROFWEEKISO. The session parameter WEEK_OF_YEAR_POLICY is set to indicate that the first week of the year is the week that contains January 1st of that year.

ALTER SESSION SET WEEK_OF_YEAR_POLICY = 1;
SELECT 
       '2016-01-02T23:39:20.123-07:00'::TIMESTAMP AS tstamp,
       WEEK(tstamp) AS "WEEK",
       WEEKISO(tstamp) AS "WEEK ISO",
       WEEKOFYEAR(tstamp) AS "WEEK OF YEAR",
       YEAROFWEEK(tstamp) AS "YEAR OF WEEK",
       YEAROFWEEKISO(tstamp) AS "YEAR OF WEEK ISO"
       ;
+-------------------------+------+----------+--------------+--------------+------------------+
| TSTAMP                  | WEEK | WEEK ISO | WEEK OF YEAR | YEAR OF WEEK | YEAR OF WEEK ISO |
|-------------------------+------+----------+--------------+--------------+------------------|
| 2016-01-02 23:39:20.123 |    1 |       53 |            1 |         2016 |             2015 |
+-------------------------+------+----------+--------------+--------------+------------------+

The following also demonstrates the use of the functions WEEK, WEEKISO, WEEKOFYEAR, YEAROFWEEK, and YEAROFWEEKISO. The session parameter WEEK_OF_YEAR_POLICY is set to indicate that the first week of the year is the first week of the year that contains at least 4 days from that year. (So, for example, the week December 26, 2010 through January 1st, 2011 is considered the last week of 2010, not the first week of 2011, because although it contains January 1st, 2011, less than half of the week is in 2011.)

ALTER SESSION SET WEEK_OF_YEAR_POLICY = 0;
SELECT 
       '2016-01-02T23:39:20.123-07:00'::TIMESTAMP AS tstamp,
       WEEK(tstamp) AS "WEEK",
       WEEKISO(tstamp) AS "WEEK ISO",
       WEEKOFYEAR(tstamp) AS "WEEK OF YEAR",
       YEAROFWEEK(tstamp) AS "YEAR OF WEEK",
       YEAROFWEEKISO(tstamp) AS "YEAR OF WEEK ISO"
       ;
+-------------------------+------+----------+--------------+--------------+------------------+
| TSTAMP                  | WEEK | WEEK ISO | WEEK OF YEAR | YEAR OF WEEK | YEAR OF WEEK ISO |
|-------------------------+------+----------+--------------+--------------+------------------|
| 2016-01-02 23:39:20.123 |   53 |       53 |           53 |         2015 |             2015 |
+-------------------------+------+----------+--------------+--------------+------------------+

The following demonstrates the use of the functions DAYOFWEEK and DAYOFWEEKISO. The session parameter WEEK_START is set to indicate that the week starts on Sunday.

ALTER SESSION SET WEEK_START = 7;
SELECT 
       '2016-01-02T23:39:20.123-07:00'::TIMESTAMP AS tstamp,
       DAYOFWEEK(tstamp) AS "DAY OF WEEK",
       DAYOFWEEKISO(tstamp) AS "DAY OF WEEK ISO"
       ;
+-------------------------+-------------+-----------------+
| TSTAMP                  | DAY OF WEEK | DAY OF WEEK ISO |
|-------------------------+-------------+-----------------|
| 2016-01-02 23:39:20.123 |           7 |               6 |
+-------------------------+-------------+-----------------+

The following demonstrates the use of the functions DAYOFWEEK and DAYOFWEEKISO. The session parameter WEEK_START is set to indicate that the week starts on Monday.

ALTER SESSION SET WEEK_START = 0;
SELECT 
       '2016-01-02T23:39:20.123-07:00'::TIMESTAMP AS tstamp,
       DAYOFWEEK(tstamp) AS "DAY OF WEEK",
       DAYOFWEEKISO(tstamp) AS "DAY OF WEEK ISO"
       ;
+-------------------------+-------------+-----------------+
| TSTAMP                  | DAY OF WEEK | DAY OF WEEK ISO |
|-------------------------+-------------+-----------------|
| 2016-01-02 23:39:20.123 |           6 |               6 |
+-------------------------+-------------+-----------------+

For more examples, see Using Dates and Timestamps.

For more detailed examples of the week-related functions (DAYOFWEEK, WEEK, WEEKOFYEAR, YEAROFWEEK, etc.), see Calendar Weeks and Weekdays.