In Oracle Database, we can use the TH format element to add the ordinal number suffix to the result of a TO_CHAR() operation when formatting dates.

For example, instead of outputting 10 Feb we could output 10th Feb. Or, instead of outputting 21 century, we can output 21st century. The same applies for spelled date components. For example, instead of Twenty One, we can output Twenty First.

Example

Here’s an example to demonstrate:

SELECT TO_CHAR(DATE '2030-12-01', 'Ddth Month')
FROM DUAL;

Result:

01st December

We can use the fm format modifier to remove the leading zero if required:

SELECT TO_CHAR(DATE '2030-12-01', 'fmDdth Month')
FROM DUAL;

Result:

1st December

Note that the capitalisation of the ordinal number is determined by the relevant format element (in this case, the Dd part), not the TH suffix.

Here’s what happens when we change the second d to uppercase:

SELECT TO_CHAR(DATE '2030-12-01', 'fmDDth Month')
FROM DUAL;

Result:

1ST December

So even though we provided the th suffix in lowercase, it had no affect on the result. Capitalisation was determined by the format element (not including the fm format modifier).

We can also add the ordinal number when returning other date components, such as the century:

SELECT TO_CHAR(DATE '2030-12-30', 'Ccth "Century"')
FROM DUAL;

Result:

21st Century

Spelled Dates

We can add the SP format element to have the date part spelled out:

SELECT TO_CHAR(DATE '2030-12-30', 'CCTHSP "Century"')
FROM DUAL;

Result:

TWENTY-FIRST Century

And we can use capitalisation as required:

SELECT TO_CHAR(DATE '2030-12-30', 'Ccthsp "Century"')
FROM DUAL;

Result:

Twenty-First Century

Here’s what happens when we omit the TH format specifier:

SELECT TO_CHAR(DATE '2030-12-30', 'Ccsp')
FROM DUAL;

Result:

Twenty-One