SQL Date Functions - EXTRACT Function
The EXTRACT function is used to extract a specified part of a date or time value. It returns an integer value.
Syntax
EXTRACT(unit FROM date)
Where unit can be one of the following:
| Unit | Description |
|---|---|
| YEAR | Returns the year of the date or time value. |
| QUARTER | Returns the quarter of the year (1 to 4) of the date or time value. |
| MONTH | Returns the month of the date or time value (1 to 12). |
| WEEK | Returns the week number of the year (1 to 53) of the date or time value. |
| DAY | Returns the day of the month (1 to 31) of the date or time value. |
| HOUR | Returns the hour of the date or time value (0 to 23). |
| MINUTE | Returns the minute of the date or time value (0 to 59). |
| SECOND | Returns the second of the date or time value (0 to 59). |
| MILLISECOND | Returns the millisecond of the date or time value (0 to 999). |
Example
SELECT EXTRACT(YEAR FROM '2018-09-17');
SELECT EXTRACT(MONTH FROM '2018-09-17 14:45:30');
SELECT EXTRACT(HOUR FROM '2018-09-17 14:45:30.123');
Output
The above SQL statements will produce the following output:
| Query | Output |
|---|---|
| SELECT EXTRACT(YEAR FROM '2018-09-17'); | 2018 |
| SELECT EXTRACT(MONTH FROM '2018-09-17 14:45:30'); | 9 |
| SELECT EXTRACT(HOUR FROM '2018-09-17 14:45:30.123'); | 14 |
Explanation
The first SQL statement extracts the year from the date value '2018-09-17' and returns the integer value '2018'.
The second SQL statement extracts the month from the date and time value '2018-09-17 14:45:30' and returns the integer value '9'.
The third SQL statement extracts the hour from the date and time value '2018-09-17 14:45:30.123' and returns the integer value '14'.
Use
The EXTRACT function is useful when you need to extract specific parts of a date or time value for grouping or filtering purposes.
For example, you can use the EXTRACT function to group sales data by month or year, or to filter records based on a specific date range.
Important Points
- The EXTRACT function only works with date and time values that are in a valid format.
- The date or time value can be a string literal, a column or a variable.
- The output value is an integer.
- The unit parameter is case insensitive.
Summary
The EXTRACT function in SQL is used to extract a specified part of a date or time value. It returns an integer value and can extract parts such as year, month day, etc. The function is useful for grouping and filtering based on specific parts of a date or time value.