Oracle Database Dates 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:
- Using
CAST( datevalue AS TIMESTAMP )to convert theDATEto aTIMESTAMPand then usingEXTRACT( [ HOUR | MINUTE | SECOND ] FROM timestampvalue ); or - Using
TO_CHAR( datevalue, format_model )to get the value as a string.
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
