In Postgres, you can use the to_char() function to return the century, based on a given date.

To do this, use CC as the second argument. This returns the two-digit century based on the date provided.

Example

Here’s an example to demonstrate.

SELECT to_char(date '2001-03-20', 'CC');

Result:

21

In this case the date is in the 21st century, so I get a result of 21.

Note that the 21st century starts on 2001-01-01.

Here’s what happens if I use a date prior to that.

SELECT to_char(date '2000-03-20', 'CC');

Result:

20

Add the Ordinal Number Suffix

You can add th or TH to the second argument in order to add the ordinal indicator to the century.

th adds a lowercase ordinal number suffix and TH adds an uppercase ordinal number suffix.

SELECT 
  to_char(date '2001-03-20', 'CCth') AS "2001 CCth",
  to_char(date '2001-03-20', 'CCTH') AS "2001 CCTH",
  to_char(date '2000-03-20', 'CCth') AS "2000 CCth",
  to_char(date '2000-03-20', 'CCTH') AS "2000 CCTH";

Result:

 2001 CCth | 2001 CCTH | 2000 CCth | 2000 CCTH 
-----------+-----------+-----------+-----------
 21st      | 21ST      | 20th      | 20TH

Append “Century”

The century value and its ordinal indicator can now be concatenated with the string “Century” if required.

SELECT 
  concat(to_char(date '2001-03-20', 'CCth'), ' Century!');

Result:

21st Century!