PL/SQL EXTRACT function for oracle datetime

The EXTRACT function extracts and returns the value of a specified datetime field from a datetime.It can also be used for extracting specified value from an interval value expression.

This can be used to extract the following from a specified datetime field.
YEAR,MONTH,DAY,HOUR,MINUTE,SECOND
TIMEZONE_HOUR,TIMEZONE_MINUTE,TIMEZONE_REGION,TIMEZONE_ABBR

EXAMPLES :

Extracting Year

SQL> select EXTRACT(YEAR FROM DATE '2008-11-11')YEAR from dual;
     YEAR
    2008
SQL>

Extracting Minute

SQL> select EXTRACT(MINUTE FROM TIMESTAMP '2008-11-11 11:32:20')MINUTE from dual;
MINUTE
 32

SQL>

Extracting TIMEZONE_REGION

SQL> select EXTRACT(TIMEZONE_REGION FROM TIMESTAMP '01-01-08 11:53:42 Pacific/Midway')TIMEZONE_REGION from dual;<
TIMEZONE_REGION
Pacific/Midway

SQL>

Also Read,

• ALTER TABLE to ADD PRIMARY KEY in Oracle| Oracle ALTER TABLE
• oracle date difference | date difference in sql an…
• DATE Data type in Oracle|Oracle Dates and Times|Dates in Oracle
• Formatting date in SQL (Oracle) -Simple date forma…

• Database Schema | Oracle Schema Objects | What is database schema?

• Database Schema | Oracle Schema Objects | What is database schema?
• Autonomous Transactions in PL/SQL
• SQL – Transaction Statements -Transaction Manageme…

• Savepoints In SQL Transactions. – SAVEPOINT
• Oracle Tablespace, and Datafiles – Introduction
• Formatting date in SQL (Oracle) -Simple date forma…
• FINDING database objects, finding valid and INVALI…

Technorati Tags:
PL/SQL EXTRACT function, EXTRACT function, EXTRACT datetime oracle, oracle EXTRACT, TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_REGION, TIMEZONE_ABBR