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 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 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