Posted By

bbrumm on 02/18/17

Tagged

sql
Oracle

Versions (?)

Last Edited at 02/18/17 09:11am

Statistics

Viewed 832 times

Favorited by 0 user(s)

Related snippets

Oracle EXTRACT Function

/ Published in: SQL

A few examples of the EXTRACT function.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML

--Example 1
SELECT SYSDATE, 
EXTRACT(YEAR FROM SYSDATE) AS EXTRACT_EXAMPLE
FROM dual;
 
--Example 2
SELECT SYSDATE, 
EXTRACT(MONTH FROM SYSDATE) AS EXTRACT_EXAMPLE
FROM dual;
 
--Example 3
SELECT SYSDATE, 
EXTRACT(DAY FROM SYSDATE) AS EXTRACT_EXAMPLE
FROM dual;
 
--Example 4
SELECT SYSDATE, 
EXTRACT(HOUR FROM SYSDATE) AS EXTRACT_HOUR
FROM dual;
 
--Example 5
SELECT SYSTIMESTAMP, 
EXTRACT(HOUR FROM SYSTIMESTAMP) AS EXTRACT_HOUR,
EXTRACT(MINUTE FROM SYSTIMESTAMP) AS EXTRACT_MINUTE,
EXTRACT(SECOND FROM SYSTIMESTAMP) AS EXTRACT_SECOND
FROM dual;
 
--Example 6
SELECT SYSTIMESTAMP, 
EXTRACT(TIMEZONE_HOUR FROM SYSTIMESTAMP) AS EXTRACT_TIMEZONE_HOUR,
EXTRACT(TIMEZONE_MINUTE FROM SYSTIMESTAMP) AS EXTRACT_TIMEZONE_MINUTE,
EXTRACT(TIMEZONE_REGION FROM SYSTIMESTAMP) AS EXTRACT_TIMEZONE_REGION,
EXTRACT(TIMEZONE_ABBR FROM SYSTIMESTAMP) AS EXTRACT_TIMEZONE_ABBR
FROM dual;
 
--Example 7
SELECT TO_YMINTERVAL('03-02'),
EXTRACT(YEAR FROM TO_YMINTERVAL('03-02')) AS EXTRACT_YEAR,
EXTRACT(MONTH FROM TO_YMINTERVAL('03-02')) AS EXTRACT_MONTH
FROM dual;