在 MariaDB 中返回带有后缀的日期数字

本文描述了如何在 MariaDB 中返回带有后缀的日期数字。

发布于

在 MariaDB 中，您可以使用 DATE_FORMAT() 函数返回带有后缀的日期，比如 1st, 2nd 等。

DATE_FORMAT() 函数用于格式化输出日期时间值，%D 格式说明符可以返回带有后缀的天。

示例

这个语句返回 2022-12-12 了带有后缀的日期值。

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

输出：

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

这里，我们使用了 3 个格式化说明符：

  • %D: 返回带有后缀的天
  • %M: 返回月份的全名
  • %Y: 返回年份

所有的日期后缀

下面语句列出了 12 月份中所有日期的后缀：

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

输出：

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

结论

您可以在 MariaDB DATE_FORMAT() 函数中使用 %D 格式说明符返回带有后缀的日期。 此处列出了 MariaDB 所有的格式化说明符，希望能帮到你。