EXTRACT

Extracts a date or time part from a DATE , DATETIME` or ``DATETIME2 value.

Note

SQream DB also supports the SQL Server DATEPART syntax, which contains more date parts for use.

Syntax

EXTRACT( interval FROM date_expr ) --> DOUBLE

interval ::=
     YEAR
   | MONTH
   | WEEK
   | DOY
   | DAY
   | HOUR
   | MINUTE
   | SECOND
   | MILLISECONDS
   | MICROSECOND | MU
   | NANOSECOND | NS

Arguments

Parameter	Description
`interval`	An interval representing a date part. See the table below or the syntax reference above for valid date parts
`date_expr`	A `DATE` , `DATETIME` or `DATETIME2` expression

Valid date parts

Returns

A floating point representing the date part value

Notes

The HOUR, MINUTE, SECOND, and MILLISECOND date parts work on DATETIME or DATETIME2. Using them on DATE will result in an error.
The MICROSECOND and NANOSECOND date parts work only on DATETIME2. Using them on DATE or DATETIME will result in an error.

Examples

For these examples, consider the following table and contents:

CREATE TABLE cool_dates(name TEXT(40), d DATE, dt DATETIME);

INSERT INTO cool_dates VALUES ('Marty McFly goes back to this time','1955-11-05','1955-11-05 01:21:00.000')
    , ('Marty McFly came from this time', '1985-10-26', '1985-10-26 01:22:00.000')
    , ('Vesuvius erupts', '79-08-24', '79-08-24 13:00:00.000')
    , ('1997 begins', '1997-01-01', '1997-01-01')
    , ('1997 ends', '1997-12-31','1997-12-31 23:59:59.999');

Break up a `DATE` into components

master=> SELECT EXTRACT(YEAR FROM d) AS year, EXTRACT(MONTH FROM d) AS month, EXTRACT(DAY FROM d) AS day
.               FROM cool_dates;
year   | month | day
-------+-------+-----
1955.0 |  11.0 |  5.0
1985.0 |  10.0 | 26.0
  79.0 |   8.0 | 24.0
1997.0 |   1.0 |  1.0
1997.0 |  12.0 | 31.0