Extract function with DATE TIME data type values

Learn how to extract a DAY,MONTH,YEAR,HOUR,MINUTE AND SECOND from a date time data type values.

EXTRACT function extracts a date or a timestamp and returns the year, month, day, hour, minute, second, or time zone.

EXTRACT extracts a YEAR, MONTH, and DAY from a DATE data type.

EXTRACT extracts a TIMEZONE_HOUR and TIMEZONE_MINUTE from TIME_STAMP data type.

Below is the syntax for Extract function

EXTRACT([date_time_unit_field] FROM [date_time_expression] )

date_time_unit_field contains YEAR or MONTH or DAY or HOUR or MINUTE or SECOND or TIMEZONE_HOUR or TIMEZONE_MINUTE or TIMEZONE_ABBR.

date_time_expression contains inbuilt function of date data type or full date format expression

1. Inbuilt date time functions are sysdate, systimestamp, current_date,current_timestamp .
2. date format expression like ( ‘DD-Mon-YYYY HH24:MI:SS’ ).

Example queries

Extract YEAR, MONTH, DAY From Date values

SELECT SYSDATE AS CURRENT_DATE_TIME, 
       EXTRACT(Year FROM SYSDATE)    AS CURRENT_YEAR,/* Extract current year from sysdate*/
       EXTRACT(MONTH FROM SYSDATE)   AS CURRENT_MONTH,/*Extract current month from sysdate*/
       EXTRACT(DAY FROM SYSDATE)     AS CURRENT_DAY /*Extract current day from sysdate*/       
FROM Dual ;

Output
CURRENT_DATE_TIME CURRENT_YEAR CURRENT_MONTH CURRENT_DAY
11/19/2019 3:55:59 PM 2019 11 19

Notes:
you cannot extract Hour,Minute, second from date data type

SELECT SYSDATE AS CURRENT_DATE_TIME,
       EXTRACT(HOUR FROM SYSDATE),
       EXTRACT(MINUTE FROM SYSDATE),
       EXTRACT(SECOND FROM SYSDATE)
FROM Dual ;

Output

— ERROR at line 1:
— ORA-30076: invalid extract field for extract source

Extract YEAR, MONTH, DAY,HOUR,MINUTE,SECOND From TIME STAMP values

SELECT systimestamp AS CURRENT_DATE_TIME, 
       EXTRACT(Year FROM SYSTIMESTAMP)    AS CURRENT_YEAR,/* Extract current year from SYSTIMESTAMP*/
       EXTRACT(MONTH FROM SYSTIMESTAMP)   AS CURRENT_MONTH,/*Extract current month from SYSTIMESTAMP*/
       EXTRACT(DAY FROM SYSTIMESTAMP)     AS CURRENT_DAY, /*Extract current day from SYSTIMESTAMP*/
       EXTRACT(HOUR FROM SYSTIMESTAMP)    AS CURREN_HOUR,
       EXTRACT(MINUTE FROM SYSTIMESTAMP)  AS CURRENT_MINUTE,
       EXTRACT(SECOND FROM SYSTIMESTAMP)  AS CURRENT_SECOND
FROM Dual 

Output
CURRENT_DATE_TIME CURRENT_YEAR CURRENT_MONTH CURRENT_DAY CURREN_HOUR CURRENT_MINUTE CURRENT_SECOND
11/19/2019 3:55:59 PM 2019 11 19 10 28 22.518645

Extract TIME_ZONE_HOUR, TIME_ZONE_ MINUTE, TIME_ZONE_ REGION, TIME_ZONE_ ABBR From TIME STAMP values

SELECT EXTRACT(TIMEZONE_HOUR FROM SYSTIMESTAMP)           AS CURRENT_TIME_ZONE_HOUR ,
       EXTRACT(TIMEZONE_MINUTE FROM SYSTIMESTAMP)         AS CURRENT_TIME_ZONE_MINUTE ,
       EXTRACT(TIMEZONE_REGION FROM SYSTIMESTAMP)         AS CURRENT_TIME_ZONE_REGION ,   
       EXTRACT(TIMEZONE_ABBR FROM SYSTIMESTAMP)         AS CURRENT_TIME_ZONE_ABBR     
FROM DUAL;

Output
CURRENT_TIME_ZONE_HOUR CURRENT_TIME_ZONE_MINUTE CURRENT_TIME_ZONE_REGION CURRENT_TIME_ZONE_ABBR
5 30 UNKNOWN UNK