How to Spell Out the Year when Formatting a Date in Oracle

Oracle Database includes a good selection of datetime format elements. that enable us to format dates and times when using the TO_CHAR(datetime) function.

One of these (the YEAR format element) allows us to format a date with the year spelled out. For example, instead of 1972, we’d get NINETEEN SEVENTY-TWO.

We can also use SYEAR so that BC dates are prefixed with a minus sign (-).

Example

Here’s an example to demonstrate:

SELECT TO_CHAR(DATE '1972-01-01', 'YEAR')
FROM DUAL;

Result:

NINETEEN SEVENTY-TWO

BC Dates

As mentioned, we can use S to prefix BC dates with a minus sign (-).

Here’s an example:

SELECT TO_CHAR(DATE '-87-01-01', 'SYEAR')
FROM DUAL;

Result:

-EIGHTY-SEVEN