DATEPART

Extracts a date or time part from a DATE or DATETIME value.

Note

SQream DB also supports the ANSI EXTRACT syntax.

Syntax

DATEPART( interval, date_expr ) --> INT

interval ::=
     YEAR | YYYY | YY
   | QUARTER | QQ | Q
   | MONTH | MM | M
   | DAYOFYEAR | DOY | DY | Y
   | DAY | DD | D
   | WEEK | WK | WW
   | WEEKDAY | DW
   | HOUR | HH
   | MINUTE | MI | N
   | SECOND | SS | S
   | MILLISECOND | MS

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	Shorthand	Definition
`YEAR`	`YYYY`, `YY`	Year (0 - 9999)
`QUARTER`	`QQ`, `Q`	Quarter (1-4)
`MONTH`	`MM`, `M`	Month (1-12)
`DAYOFYEAR`	`DOY`, `DY`, `Y`	Day of the year (1-365)
`DAY`	`DD`, `D`	Day of the month (1-31)
`WEEK`	`WK`, `WW`	Week of the year (1-52)
`WEEKDAY`	`DW`	Weekday / Day of week (1-7)
`HOUR`	`HH`	Hour (0-23)
`MINUTE`	`MI`, `N`	Minute (0-59)
`SECOND`	`SS`, `S`	Seconds (0-59)
`MILLISECOND`	`MS`	Milliseconds (0-999)

Note

The first day of the week is Sunday, when used with WEEKDAY.

Returns

An integer representing the date part value

Notes

All date parts work on a DATETIME.
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 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 DATEPART(YEAR, d) AS  year, DATEPART(MONTH, d) AS month, DATEPART(DAY,d) AS day,
.               DATEPART(Q,d) AS quarter FROM cool_dates;
year | month | day | quarter
-----+-------+-----+--------
1955 |    11 |   5 |       4
1985 |    10 |  26 |       4
  79 |     8 |  24 |       3
1997 |     1 |   1 |       1
1997 |    12 |  31 |       4

Break up a `DATETIME` into time components

master=> SELECT DATEPART(HOUR, dt) AS hour, DATEPART(MINUTE, dt) AS minute,
.               DATEPART(SECOND,dt) AS seconds, DATEPART(MILLISECOND,dt) AS milliseconds
.               FROM cool_dates;
hour | minute | seconds | milliseconds
-----+--------+---------+-------------
   1 |     21 |       0 |            0
   1 |     22 |       0 |            0
  13 |      0 |       0 |            0
   0 |      0 |       0 |            0
  23 |     59 |      59 |          999

Count number of rows grouped by quarter

Tip

Use ordinal aliases to avoid having to write complex functions in the GROUP BY clause. See Select lists for more information.

master=> SELECT COUNT(*), DATEPART(Q, dt) AS quarter FROM cool_dates GROUP BY 2;

count | quarter
------+--------
    1 |       1
    1 |       3
    3 |       4