在 MariaDB 中返回带英文序数后缀的日期(如 1st、2nd、3rd)
介绍在 MariaDB 中使用 DATE_FORMAT() 函数配合 %D 说明符,将日期中的天数格式化为带有英文序数词后缀(如 1st、2nd、12th、23rd)的详细用法与完整对照表。
发布于
在英文排版或面向海外用户的报表输出中,日期中的“日”常常需要表示为带有序数词后缀的格式,例如 1st、2nd、3rd、4th、21st 等。
在 MariaDB 中,无需编写繁琐的 CASE WHEN 逻辑,直接使用内置的 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 |
+---------------------------------------+上述格式字符串中各说明符的作用如下:
%D:返回带有英文序数词后缀的天(如1st,2nd,12th)。%M:返回月份的全名(如December)。%Y:返回 4 位数字的年份(如2022)。
常见天数格式说明符对比
为了更好地满足不同的业务排版需求,MariaDB 提供了三种获取“天”的说明符:
| 说明符 | 含义与示例 | 适用场景 |
|---|---|---|
%D |
带有英文序数后缀的日(1st, 2nd, …, 31st) |
英文文案、通知信件、新闻发布日期展示 |
%d |
两位数字的日,不足两位前导补零(01 至 31) |
标准 ISO 日期格式、结构化导出文件 |
%e |
一位或两位数字的日,不补零(1 至 31) |
日常紧凑展示 |
以下 SQL 语句展示了这三者的输出差异:
SELECT
DATE_FORMAT('2022-12-03', '%D') AS "with_suffix",
DATE_FORMAT('2022-12-03', '%d') AS "padded_zero",
DATE_FORMAT('2022-12-03', '%e') AS "no_padding";
输出:
+-------------+-------------+------------+
| with_suffix | padded_zero | no_padding |
+-------------+-------------+------------+
| 3rd | 03 | 3 |
+-------------+-------------+------------+1 至 31 日英文序数后缀完整对照
在英语语法中,除特殊规律的 11th、12th、13th 之外,末尾数字为 1 用 st,2 用 nd,3 用 rd,其余数字用 th。MariaDB 的 %D 说明符已经完整内置了这些规则:
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[!NOTE]
%D说明符专门生成英文序数后缀(st,nd,rd,th),不受当前会话lc_time_names语言变量的影响。
总结
在 MariaDB 中生成带英文序数后缀的日期,只需在 DATE_FORMAT() 函数中使用 %D 说明符。如需查看 MariaDB 支持的全部日期时间格式符,请参阅 MariaDB 日期格式化说明符速查表。