MariaDB includes a large collection of date and time functions that return a given date in a certain format.

One thing you can do is return the day number with the relevant “st/nd/rd/th” suffix. For example, instead of returning it as say, 10 July 2025, it’s returned as 10th July 2025.

Below is an example of adding the relevant suffix to a day number in MariaDB.

Example

To return the day number with the relevant suffix, you can use the DATE_FORMAT() function.

This function accepts a date and a format string. The format string specifies how the given date should be formatted. The format string consists of one or more format specifiers.

The %D format specifier returns the day with English suffix. So including this format specifier will return the day with one of st, nd, rd, or th appended, depending on the actual day number.

Example:

SELECT DATE_FORMAT('2030-01-01', '%D %M %Y');

Result:

+---------------------------------------+
| DATE_FORMAT('2030-01-01', '%D %M %Y') |
+---------------------------------------+
| 1st January 2030                      |
+---------------------------------------+

Here, I combined three format specifiers (%D %M %Y) to produce a full date. In this case, the day number was 1 and so st is automatically appended.

Here are some more examples, to demonstrate the various suffixes:

SELECT 
    DATE_FORMAT('2030-01-01', '%D') AS "1",
    DATE_FORMAT('2030-01-02', '%D') AS "2",
    DATE_FORMAT('2030-01-03', '%D') AS "3",
    DATE_FORMAT('2030-01-04', '%D') AS "4",
    DATE_FORMAT('2030-01-20', '%D') AS "20",
    DATE_FORMAT('2030-01-21', '%D') AS "21",
    DATE_FORMAT('2030-01-22', '%D') AS "22",
    DATE_FORMAT('2030-01-23', '%D') AS "23",
    DATE_FORMAT('2030-01-24', '%D') AS "24";

Result:

+------+------+------+------+------+------+------+------+------+
| 1    | 2    | 3    | 4    | 20   | 21   | 22   | 23   | 24   |
+------+------+------+------+------+------+------+------+------+
| 1st  | 2nd  | 3rd  | 4th  | 20th | 21st | 22nd | 23rd | 24th |
+------+------+------+------+------+------+------+------+------+

In this case I only use one format specifier (%D) just to keep the example concise, but the format string can contain any number of format specifiers. See this list of MariaDB format specifiers for more.