Dev Corner

Software Developer’s Notepad

To extract date parts in PL/SQL you can use the TO_CHAR function. For example, you can get the month number from a date value with the following expression TO_CHAR(date_value, 'mm');. There is better approach. You can use the EXTRACT function to get the date part you need.

The Oracle PL/SQL function EXTRACT extracts a date/time part from a date or interval value.

The syntax for the EXTRACT function is:

EXTRACT (
     { YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }
       | TIMEZONE_HOUR | TIMEZONE_MINUTE }
       | TIMEZONE_REGION | TIMEZONE_ABBR }
  FROM { date_value | interval_value } )

You can extract YEAR, MONTH or DAY only if the argument is of DATE type.

Example 1: To get the day of birth from a birth_date in Oracle Database table you use EXTRACT(DAY FROM birth_date):

SELECT first_name, 
           EXTRACT(DAY FROM birth_date) AS birth_day
  FROM employee;

Example 2: To extract the year an employee was born from the employee table in an Oracle Database you use EXTRACT(YEAR FROM birth_date):

SELECT first_name, 
           EXTRACT(YEAR FROM birth_date) AS year_born
  FROM employee;

Add A Comment

You must be logged in to post a comment.