Extracting hour and minute from a TIMESTAMP and DATE variable

Environment: Oracle 10g (10.2.0.5), Oracle 11gR2

To extract the HOUR and MINUTE parts from a TIMESTAMP column. Oracle offers the EXTRACT(HOUR FROM …) and EXTRACT(MINUTE FROM …) functions.

This is an example using SYSTIMESTAMP:

SELECT EXTRACT(HOUR FROM SYSTIMESTAMP) AS CURRENT_HOUR FROM DUAL;

SELECT EXTRACT(MINUTE FROM SYSTIMESTAMP) AS CURRENT_MINUTE FROM DUAL;

 

To extract the HOUR and MINUTE parts from a DATE column. Oracle offers the TO_CHAR(date, ‘HH24′) and TO_CHAR(date,’MI’) functions.

This is an example using SYSDATE:

SELECT TO_CHAR(SYSDATE,’HH24′) AS CURRENT_HOUR FROM DUAL;

SELECT TO_CHAR(SYSDATE,’MI’) AS CURRENT_MINUTE FROM DUAL;

 

Matthias

Advertisements