EXTRACT() — Returns the value of a selected portion of a timestamp or date.
EXTRACT( selection-keyword FROM timestamp-or-date )
EXTRACT( selection-keyword, timestamp-or-date )
The EXTRACT() function returns the value of the selected portion of a timestamp or date. Both timestamp and date expressions are allowable input for date selections. However, only timestamp values are valid for selecting the HOUR, MINUTE, or SECOND portions of a time value. Table C.1, “Selectable Values for the EXTRACT Function” lists the supported keywords, the datatype of the value returned by the function, and a description of its contents.
Table C.1. Selectable Values for the EXTRACT Function
Keyword | Datatype | Description |
---|---|---|
YEAR | INTEGER | The year as a numeric value. |
QUARTER | TINYINT | The quarter of the year as a single numeric value between 1 and 4. |
MONTH | TINYINT | The month of the year as a numeric value between 1 and 12. |
DAY | TINYINT | The day of the month as a numeric value between 1 and 31. |
DAY_OF_MONTH | TINYINT | The day of the month as a numeric value between 1 and 31 (same as DAY). |
DAY_OF_WEEK | TINYINT | The day of the week as a numeric value between 1 and 7, starting with Sunday. |
DAY_OF_YEAR | SMALLINT | The day of the year as a numeric value between 1 and 366. |
WEEK | TINYINT | The week of the year as a numeric value between 1 and 52. |
WEEK_OF_YEAR | TINYINT | The week of the year as a numeric value between 1 and 52 (same as WEEK). |
WEEKDAY | TINYINT | The day of the week as a numeric value between 0 and 6, starting with Monday. |
HOUR | TINYINT | The hour of the day as a numeric value between 0 and 23. |
MINUTE | TINYINT | The minute of the hour as a numeric value between 0 and 59. |
SECOND | DECIMAL | The whole and fractional part of the number of seconds within the minute as a floating point value between 0 and 60. |
The following example lists all the contacts by name and birthday, listing the birthday as three separate fields for month, day, and year.
SELECT Last_name, first_name, EXTRACT(MONTH FROM dateofbirth), EXTRACT(DAY FROM dateofbirth), EXTRACT(YEAR FROM dateofbirth) FROM contact_list ORDER BY last_name, first_name;