With Oracle Database, we can use the TO_CHAR(datetime) function to return the century from a datetime value.

To do this, we can use either the CC format element, or the SCC format element. We can combine these with other format elements to do things like, display the ordinal number, spell out the century, etc.

Example

Here’s an example that uses the CC format element to return the century from a date value:

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

Result:

21

Negative Centuries

We can use the SCC format element to include the minus sign when passing a negative date:

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

Result:

-21

Ordinal Number

We can add the TH format element to return the ordinal number of the century.

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

Result:

21ST

We can also add arbitrary text to append the word “Century”:

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

Result:

21ST Century

Note that the text is surrounded by double quotes.

We can use capitalisation as required:

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

Result:

21st Century

Notice that I only capitalised the first character in the format model. What I mean is that I used Ccth instead of CCth. While you might think that CCth should result in lowercase ordinal number, that’s not how it works. Here’s what happens when we do that:

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

Result:

21ST Century

Basically, when the first two characters of the format model are uppercase, the result is uppercase. Therefore, to produce title case, only capitalise the first character of the format model.

Spelled Century

We can add the SP format element to have the century 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

We can omit the TH format specifier if we don’t want the ordinal number:

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

Result:

Twenty-One

20th vs 21st Centuries

The following rules apply with regards to when a date is considered to be in the 20th century versus the 21st century.

• If the last 2 digits of a 4-digit year are between 01 and 99 (inclusive), then the century is one greater than the first 2 digits of that year.
• If the last 2 digits of a 4-digit year are 00, then the century is the same as the first 2 digits of that year.

The same concept applies to other centuries, such as the 13th century, 30th century, etc.

Example:

SELECT 
    TO_CHAR(DATE '1999-12-30', 'CC') AS "1999",
    TO_CHAR(DATE '2000-12-30', 'CC') AS "2000",
    TO_CHAR(DATE '1299-12-30', 'CC') AS "1299",
    TO_CHAR(DATE '1300-12-30', 'CC') AS "1300",
    TO_CHAR(DATE '2999-12-30', 'CC') AS "2999",
    TO_CHAR(DATE '3000-12-30', 'CC') AS "3000"
FROM DUAL;

Result:

   1999    2000    1299    1300    2999    3000 
_______ _______ _______ _______ _______ _______ 
20      20      13      13      30      30     

There are many more format elements available for formatting datetime values in Oracle. See List of Datetime Format Elements in Oracle for a full list.