SQL EXTRACT (Oracle Function)

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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>