The SQL EXTRACT() function is used to get a value (year, month, day, etc.) from a date/time expression. It’s supported by MySQL and Oracle, but the syntax is different. In this post we’ll discuss it’s syntax and example in Oracle.
SQL EXTRACT (Oracle Function) Syntax
EXTRACT (
{ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }
| { TIMEZONE_HOUR | TIMEZONE_MINUTE }
| { TIMEZONE_REGION | TIMEZONE_ABBR }
FROM { date_expression | interval_value_expression } )
Note: in Orcale, you can only extract YEAR, MONTH, or DAY, which is not like in MySQL, you can extract hour, minute, second and more.
SQL EXTRACT (Oracle Function) Example
SELECT EXTRACT(YEAR FROM DATE '2010-08-07') AS Year FROM DUAL
The result will look like:
| Year |
|---|
| 2010 |
SELECT EXTRACT(YEAR FROM TO_DATE('08/07/2011','MM/DD/YYYY')) AS Year FROM DUAL
The result will look like:
| Year |
|---|
| 2011 |