EXTRACT

Extracts a date or time part from a DATE or DATETIME 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

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 or DATETIME expression

Valid date parts

Date part

Definition

YEAR

Year (0.0 - 9999.0)

MONTH

Month (1.0-12.0)

DOY

Day of the year (1.0-365.0)

DAY

Day of the month (1.0-31.0)

WEEK

Week of the year (1.0-52.0)

HOUR

Hour (0.0-23.0)

MINUTE

Minute (0.0-59.0)

SECOND

Seconds (0.0-59.0)

MILLISECONDS

Milliseconds (0.0-999.0)

Returns

  • A floating point representing the date part value

Notes

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

Examples

For these examples, consider the following table and contents:

CREATE TABLE cool_dates(name VARCHAR(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