Add the Ordinal Indicator to a Date in PostgreSQL

The PostgreSQL to_char() function provides us with the option of adding the ordinal number suffix to the result.

This means we can add the ordinal indicator when formatting dates.

For example, instead of outputting 10 Feb we could output 10th Feb.

Template Pattern Modifiers

The following template pattern modifiers can be used to apply the ordinal number suffix.

Modifier	Description
`th`	Lowercase ordinal number suffix.
`TH`	Uppercase ordinal number suffix.

Here’s an example of usage.

SELECT to_char(date '2001-03-25', 'DDth');

Result:

25th

Note that it will automatically adjust the suffix between “th”, “st”, “rd”, etc depending on the actual date value.

For example, here’s what happens if I change the date from the previous example.

SELECT to_char(date '2001-03-01', 'DDth');

Result:

01st

We can modify the previous example to suppress the leading zero.

To do that, prefix the template modifier with fm.

SELECT to_char(date '2001-03-01', 'fmDDth');

Result:

1st

Here’s an example that builds on the previous example so that we output a more complete date.

SELECT to_char(date '2001-03-01', 'Day, fmDDth Month YYYY');

Result:

Thursday , 1st March     2001

The ordinal number suffix can also be used when displaying the century.

SELECT 
  concat(to_char(date '2019-10-23', 'CCth'), ' Century');

Result:

21st Century