如何将混合12/24小时制的datetime列统一转换为24小时制

有一张MESSY_TABLE表，其中date_time列存储的是混合格式的字符串：既有带AM/PM标识的12小时制时间，也有24小时制时间，示例数据如下：

需要将这些值统一转换为24小时制的datetime类型，存入NEW_TABLE，最终结果如下：

以下是主流数据库的实现方案：

SQL Server 解决方案 #

注意：原代码中同时使用CREATE TABLE和SELECT INTO会报错（SELECT INTO会自动创建新表），以下是修正后的转换代码：

-- 若NEW_TABLE已存在则删除（可选）
IF OBJECT_ID('NEW_TABLE', 'U') IS NOT NULL
    DROP TABLE NEW_TABLE;

SELECT
    ID,
    -- 根据字符串是否包含AM/PM选择对应转换规则
    CASE
        WHEN date_time LIKE '%AM' OR date_time LIKE '%PM'
        THEN TRY_CONVERT(DATETIME, date_time, 100) -- 适配带AM/PM的12小时制格式
        ELSE TRY_CONVERT(DATETIME, date_time, 120) -- 适配24小时制格式
    END AS date_time
INTO NEW_TABLE
FROM MESSY_TABLE;

-- 查询时输出指定格式的24小时制字符串
SELECT
    ID,
    FORMAT(date_time, 'MM/dd/yyyy HH:mm') AS date_time
FROM NEW_TABLE;

说明 #

• TRY_CONVERT函数会尝试按指定格式转换字符串，失败则返回NULL（可根据需求处理异常值）
• 格式代码100兼容mm/dd/yyyy hh:mm:ss PM这类带AM/PM的12小时制格式
• 格式代码120兼容mm/dd/yyyy HH:mm这类24小时制格式
• FORMAT函数用于将datetime类型输出为你需要的MM/dd/yyyy HH:mm样式

MySQL 解决方案 #

-- 创建目标表（若不存在）
CREATE TABLE IF NOT EXISTS NEW_TABLE (
    ID INT,
    date_time DATETIME
);

-- 插入转换后的数据
INSERT INTO NEW_TABLE (ID, date_time)
SELECT
    ID,
    CASE
        WHEN date_time REGEXP 'AM|PM'
        THEN STR_TO_DATE(date_time, '%m/%d/%Y %h:%i:%s %p') -- 转换12小时制带AM/PM的字符串
        ELSE STR_TO_DATE(date_time, '%m/%d/%Y %H:%i') -- 转换24小时制字符串
    END AS date_time
FROM MESSY_TABLE;

-- 查询时输出指定格式的24小时制字符串
SELECT
    ID,
    DATE_FORMAT(date_time, '%m/%d/%Y %H:%i') AS date_time
FROM NEW_TABLE;

说明 #

• STR_TO_DATE函数按指定格式将字符串转为DATETIME类型
• 格式符%h表示12小时制小时，%p匹配AM/PM；%H表示24小时制小时
• DATE_FORMAT函数用于将DATETIME类型格式化为目标字符串样式

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