PLSQL, Oracle forms & Reports Tutorials

𝐓𝐡𝐞 𝐟𝐨𝐥𝐥𝐨𝐰𝐢𝐧𝐠 𝐞𝐱𝐚𝐦𝐩𝐥𝐞 𝐞𝐱𝐭𝐫𝐚𝐜𝐭𝐬 𝐭𝐡𝐞 𝐯𝐚𝐥𝐮𝐞 𝐨𝐟 𝐭𝐡𝐞 𝐘𝐄𝐀𝐑 𝐟𝐢𝐞𝐥𝐝 𝐟𝐫𝐨𝐦 𝐚 𝐃𝐀𝐓𝐄 𝐯𝐚𝐥𝐮𝐞

SELECT
EXTRACT( YEAR FROM TO_DATE( '31-Dec-2022 15:30:20 ', 'DD-Mon-YYYY HH24:MI:SS' ) ) YEAR
FROM
DUAL;

In this example, we used the TO_DATE() function to convert a date literal to a DATE value.

Here is the result: 2022

Similarly, you can extract the values of other fields as shown below:
𝐄𝐱𝐭𝐫𝐚𝐜𝐭𝐢𝐧𝐠 𝐦𝐨𝐧𝐭𝐡 𝐟𝐫𝐨𝐦 𝐚 𝐝𝐚𝐭𝐞:

SELECT
EXTRACT( MONTH FROM TO_DATE( '23-Feb-2023 15:30:20 ', 'DD-Mon-YYYY HH24:MI:SS' ) ) YEAR
FROM
DUAL;

Here is the result: 02

𝐄𝐱𝐭𝐫𝐚𝐜𝐭𝐢𝐧𝐠 𝐝𝐚𝐲 𝐟𝐫𝐨𝐦 𝐚 𝐝𝐚𝐭𝐞:

SELECT
EXTRACT( DAY FROM TO_DATE( '23-Feb-2023 15:30:20 ', 'DD-Mon-YYYY HH24:MI:SS' ) ) YEAR
FROM
DUAL;

Here is the result: 23

To extract values of HOUR, MINUTE, and SECOND fields, you use TO_CHAR() function.

For example, to extract hour, minute, and second of a current system date, you use the following statement:

SELECT
TO_CHAR( SYSDATE, 'HH24' ) hour,
TO_CHAR( SYSDATE, 'MI' ) minute,
TO_CHAR( SYSDATE, 'SS' ) second
FROM
DUAL;