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

2 Responses to Extracting hour and minute from a TIMESTAMP and DATE variable

  1. Jan Doornaert says:

    Matthias,

    Why not just use to_char(sysdate, ‘HH24’) and to_char(sysdate, ‘MI’) ?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: