How to get the day with a suffix in MariaDB

This article describes how to return date numbers with suffixes in MariaDB.

Posted on

In MariaDB, you can use the DATE_FORMAT() function to return days with suffixes such as 1st, 2nd etc.

The DATE_FORMAT() function is used to format output datetime values, the %D format specifiers can return days with a suffix.

Example 1

This statement returns the day ​​with a suffix from 2022-12-12.

SELECT DATE_FORMAT('2022-12-12', '%D %M %Y');

Output:

+---------------------------------------+
| DATE_FORMAT('2022-12-12', '%D %M %Y') |
+---------------------------------------+
| 12th December 2022                    |
+---------------------------------------+

Here, we use 3 format specifiers:

  • %D: returns the day with the suffix
  • %M: returns the long month name
  • %Y: returns the year

Get all days with suffixes

The following statement lists all days with suffixes in December:

SELECT
  DATE_FORMAT('2022-12-01', '%D') AS "01",
  DATE_FORMAT('2022-12-02', '%D') AS "02",
  DATE_FORMAT('2022-12-03', '%D') AS "03",
  DATE_FORMAT('2022-12-04', '%D') AS "04",
  DATE_FORMAT('2022-12-05', '%D') AS "05",
  DATE_FORMAT('2022-12-06', '%D') AS "06",
  DATE_FORMAT('2022-12-07', '%D') AS "07",
  DATE_FORMAT('2022-12-08', '%D') AS "08",
  DATE_FORMAT('2022-12-09', '%D') AS "09",
  DATE_FORMAT('2022-12-10', '%D') AS "10",
  DATE_FORMAT('2022-12-11', '%D') AS "11",
  DATE_FORMAT('2022-12-12', '%D') AS "12",
  DATE_FORMAT('2022-12-13', '%D') AS "13",
  DATE_FORMAT('2022-12-14', '%D') AS "14",
  DATE_FORMAT('2022-12-15', '%D') AS "15",
  DATE_FORMAT('2022-12-16', '%D') AS "16",
  DATE_FORMAT('2022-12-17', '%D') AS "17",
  DATE_FORMAT('2022-12-18', '%D') AS "18",
  DATE_FORMAT('2022-12-19', '%D') AS "19",
  DATE_FORMAT('2022-12-20', '%D') AS "20",
  DATE_FORMAT('2022-12-21', '%D') AS "21",
  DATE_FORMAT('2022-12-22', '%D') AS "22",
  DATE_FORMAT('2022-12-23', '%D') AS "23",
  DATE_FORMAT('2022-12-24', '%D') AS "24",
  DATE_FORMAT('2022-12-25', '%D') AS "25",
  DATE_FORMAT('2022-12-26', '%D') AS "26",
  DATE_FORMAT('2022-12-27', '%D') AS "27",
  DATE_FORMAT('2022-12-28', '%D') AS "28",
  DATE_FORMAT('2022-12-29', '%D') AS "29",
  DATE_FORMAT('2022-12-30', '%D') AS "30",
  DATE_FORMAT('2022-12-31', '%D') AS "31"\G

Output:

01: 1st
02: 2nd
03: 3rd
04: 4th
05: 5th
06: 6th
07: 7th
08: 8th
09: 9th
10: 10th
11: 11th
12: 12th
13: 13th
14: 14th
15: 15th
16: 16th
17: 17th
18: 18th
19: 19th
20: 20th
21: 21st
22: 22nd
23: 23rd
24: 24th
25: 25th
26: 26th
27: 27th
28: 28th
29: 29th
30: 30th
31: 31st

Conclusion

You can use the DATE_FORMAT() function and the %D format specifier to return the day with a suffix. All MariaDB format specifiers are listed here , hoping to help you.