Below are two functions that can be used to extract the year from a date in Oracle Database.

The EXTRACT() Function

You can use the EXTRACT(datetime) function to extract various datetime parts from a datetime value. This includes the year.

Here’s an example:

SELECT EXTRACT(YEAR FROM DATE '2030-12-25')
FROM DUAL;

Result:

2030

It’s the YEAR keyword that extracts the year part from the date. We can get other date parts by changing it to the relevant keyword. For example, MONTH, DAY, HOUR, MINUTE, etc.

We can also use the function with other datetime values, such as TIMESTAMP, etc.

The function can also be used to return the year from an interval. See EXTRACT(datetime) Function in Oracle for an example.

The TO_CHAR(datetime) Function

We can also use the TO_CHAR(datetime) function as an alternative method to get the year from a date.

This function accepts the datetime or interval value as its first argument, and a format model as its second argument. The function then converts the value to a data type of VARCHAR2 in the specified format.

The format model specifies the format for which to return the datetime/interval value. The format model consists of one or more format elements. This enables us to carefully craft the results to reflect our desired format.

If we only want to return the year, we can use the YYYY format element:

SELECT TO_CHAR(DATE '2027-10-03', 'YYYY')
FROM DUAL;

Result:

2027

We can also use alternatives for the format model. For example, we can use YY to specify a two-digit year:

SELECT TO_CHAR(DATE '2027-10-03', 'YY')
FROM DUAL;

Result:

27

In fact, we can have anywhere between one and four Y characters, depending on our requirements:

SELECT 
    TO_CHAR(DATE '2027-10-03', 'Y') AS Y,
    TO_CHAR(DATE '2027-10-03', 'YY') AS YY,
    TO_CHAR(DATE '2027-10-03', 'YYY') AS YYY,
    TO_CHAR(DATE '2027-10-03', 'YYYY') AS YYYY
FROM DUAL;

Result:

   Y    YY    YYY    YYYY 
____ _____ ______ _______ 
7    27    027    2027   

Include a Comma

We can use the Y,YYY format element if we want to include a comma in the output:

SELECT TO_CHAR(DATE '2027-10-03', 'Y,YYY')
FROM DUAL;

Result:

2,027

Spell Out the Year

We can even use the YEAR format element to return the year spelled out:

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

Result:

TWENTY TWENTY-SEVEN

BC Dates

We can also cater to BC dates by prefixing our year format element with an S. Doing this, prefixes the result with a minus sign, as applicable:

SELECT 
    TO_CHAR(DATE '-250-10-03', 'SYYYY') AS SYYYY,
    TO_CHAR(DATE '-250-10-03', 'SYEAR') AS SYEAR
FROM DUAL;

Result:

   SYYYY         SYEAR 
________ _____________ 
-0250    -TWO FIFTY   

We can alternatively use the BC or B.C. format elements to cater for BC dates:

SELECT 
    TO_CHAR(DATE '-250-10-03', 'YYYY BC') AS YYYY,
    TO_CHAR(DATE '-250-10-03', 'YEAR BC') AS YEAR,
    TO_CHAR(DATE '250-10-03', 'YYYY BC') AS YYYY,
    TO_CHAR(DATE '250-10-03', 'YEAR BC') AS YEAR
FROM DUAL;

Result:

      YYYY            YEAR       YYYY            YEAR 
__________ _______________ __________ _______________ 
0250 BC    TWO FIFTY BC    0250 AD    TWO FIFTY AD