Oracle Database has the TO_CHAR(datetime) function that enables us to get various date parts from a datetime value.

There’s also the EXTRACT(datetime) function that extracts a specific datetime unit (e.g. day, month, year, etc).

The TO_CHAR(datetime) Function

We can get the day, month, and year from a date by passing the applicable format model to the TO_CHAR(datetime) function.

Example:

SELECT TO_CHAR(DATE '2035-09-26', 'Day, DD Month YYYY')
FROM DUAL;

Result:

Wednesday, 26 September 2035

In this case my format model specified various date parts; the full day name, the “day of the month” number, the month name, and the year. Any of the format elements can be omitted, and any number of other format elements could be added.

We can even separate each date component by making multiple calls to the function, each with a different format element:

SELECT 
    TO_CHAR(DATE '2035-09-26', 'Day') AS Day,
    TO_CHAR(DATE '2035-09-26', 'DD') AS DD,
    TO_CHAR(DATE '2035-09-26', 'Month') AS Month,
    TO_CHAR(DATE '2035-09-26', 'YYYY') AS Year
FROM DUAL;

Result:

         DAY    DD        MONTH    YEAR 
____________ _____ ____________ _______ 
Wednesday    26    September    2035    

See this Full List of Datetime Format Elements in Oracle for a list of format elements that can be used to format datetime values with this function.

The EXTRACT(datetime) Function

The EXTRACT(datetime) function is another way to return the day, month, or year from a datetime value. This function only extracts one date part at a time.

Example:

SELECT EXTRACT(YEAR FROM DATE '2027-10-03')
FROM DUAL;

Result:

2027

In this case I extracted the year from the date.

Here’s an example that extracts the day, month, and year:

SELECT 
    EXTRACT(DAY FROM DATE '2027-10-03') AS Day,
    EXTRACT(MONTH FROM DATE '2027-10-03') AS Month,
    EXTRACT(YEAR FROM DATE '2027-10-03') AS Year
FROM DUAL;

Result:

   DAY    MONTH    YEAR 
______ ________ _______ 
     3       10    2027

See EXTRACT (datetime) Function in Oracle for a list of date parts that can be extracted with this function.