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.
DocumentationSELECT 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;