Oracle Database Extract the Year, Month, Day, Hour, Minute or Second Components of a Date


Example

The year, month or day components of a DATE data type can be found using the EXTRACT( [ YEAR | MONTH | DAY ] FROM datevalue )

SELECT EXTRACT (YEAR  FROM DATE '2016-07-25') AS YEAR,
       EXTRACT (MONTH FROM DATE '2016-07-25') AS MONTH,
       EXTRACT (DAY   FROM DATE '2016-07-25') AS DAY
FROM DUAL;

Outputs:

YEAR MONTH DAY
---- ----- ---
2016     7  25

The time (hour, minute or second) components can be found by either:

For example:

SELECT EXTRACT( HOUR   FROM CAST( datetime AS TIMESTAMP ) ) AS Hours,
       EXTRACT( MINUTE FROM CAST( datetime AS TIMESTAMP ) ) AS Minutes,
       EXTRACT( SECOND FROM CAST( datetime AS TIMESTAMP ) ) AS Seconds
FROM   (
  SELECT TO_DATE( '2016-01-01 09:42:01', 'YYYY-MM-DD HH24:MI:SS' ) AS datetime FROM DUAL
);

Outputs:

HOURS MINUTES SECONDS
----- ------- -------
    9      42       1