如何在SQL中为缺失前导零的字符串列添加前导零？

核心思路是先把字符串转成数据库原生的日期时间类型，再按带前导零的格式重新输出——这种方法比手动拼接字符串靠谱多了，能搞定所有边缘情况（比如1月、1号、凌晨2点这类场景）。

MySQL/MariaDB 写法 #

用STR_TO_DATE解析字符串，再用DATE_FORMAT格式化：

SELECT DATE_FORMAT(STR_TO_DATE(your_column, '%c/%e/%Y %l:%i:%s %p'), '%m/%d/%Y %l:%i:%s %p') AS formatted_datetime
FROM your_table;

• 细节说明：
  • %c对应无前导零的月份，%e对应无前导零的日期，%l是12小时制的无前导零小时，这三个参数刚好匹配你输入的格式。
  • 输出时%m（带前导零月份）、%d（带前导零日期）、%i（带前导零分钟）会自动补全前导零，和你要的结果一致。

SQL Server 写法 #

用TRY_CONVERT转成DATETIME，再用FORMAT指定输出格式：

SELECT FORMAT(TRY_CONVERT(DATETIME, your_column, 101), 'MM/dd/yyyy h:mm:ss tt') AS formatted_datetime
FROM your_table;

• 细节说明：
  • TRY_CONVERT比CONVERT更安全，遇到格式错误的字符串会返回NULL而不是直接报错。
  • 格式串里MM/dd/mm分别保证月份、日期、分钟带前导零，h保留无前导零的小时，tt对应AM/PM标识。

PostgreSQL 写法 #

用TO_TIMESTAMP解析，TO_CHAR格式化输出：

SELECT TO_CHAR(TO_TIMESTAMP(your_column, 'MM/DD/YYYY HH12:MI:SS AM'), 'MM/DD/YYYY HH12:MI:SS AM') AS formatted_datetime
FROM your_table;

• 细节说明：
  • PostgreSQL的时间戳解析函数会自动识别无前导零的数字，比如输入的9会被当成09月，不用额外指定格式符。
  • 输出格式里的MM/DD/MI确保前导零补全，HH12维持12小时制的无前导零小时。

直接更新表数据的写法 #

如果要把修改后的值直接存回表，以MySQL为例：

UPDATE your_table
SET your_column = DATE_FORMAT(STR_TO_DATE(your_column, '%c/%e/%Y %l:%i:%s %p'), '%m/%d/%Y %l:%i:%s %p')
WHERE your_column IS NOT NULL;

其他数据库的更新逻辑类似，把对应格式函数套进去就行。

内容的提问来源于stack exchange，提问作者confused101