Extract the Year, Month, Day, Hour, Minute or Second from the Date in Oracle

For extracting the Year, Month, Day FROM Date datatype:

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

For extracting the HOUR, MINUTE, SECOND FROM timestamp datatype:

Note: CAST the (date as timestamp) before using extract function.

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( '12-07-2021 09:13:10', 'DD-MM-YYYY HH24:MI:SS' ) AS datetime FROM DUAL;

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.