PostgreSQL处理含两位/四位年份的字符串转DATE报错解决

我有一张表multi_app_documentation，其中nsma1_ans字段是varchar类型且无法修改。员工输入的日期格式五花八门，包括01232023、01/23/23、1/23/2023、01/23/2023等，既有两位年份也有四位年份。

用TO_DATE函数转换这个字段时，WHERE子句里直接报错：

ERROR: invalid value "/1" for "DD"
DETAIL: Value must be an integer.

我需要把这些日期统一转成MMDDYYYY格式，同时在WHERE子句里正确转换字段并匹配CURRENT_DATE。原查询代码如下：

SELECT 
    CASE WHEN multi_app_documentation.nsma1_code = 'DATE' THEN TO_DATE(multi_app_documentation.nsma1_ans, 'MMDDYYYY') END AS "Procedure Date",
    ' ' AS "Case Confirmation Number",
    ip_visit_1.ipv1_firstname AS "Patient First",
    ip_visit_1.ipv1_lastname AS "Patient Last",
    visit.visit_sex AS "Patient Gender",
    TO_CHAR(visit.visit_date_of_birth, 'MM/DD/YYYY') AS "DOB",
    visit.visit_id AS "Account Number",
    visit.visit_mr_num AS "MRN",
    ' ' AS "Module",
    ' ' AS "Signed off DT",
    CASE WHEN multi_app_documentation.nsma1_code = 'CRNA' THEN multi_app_documentation.nsma1_ans END AS "Primary CRNA",
    ' ' AS "Secondary CRNA",
    ' ' AS "Primary Anesthesiologist",
    ' ' AS "Secondary Anesthesiologist",
    ' ' AS "Canceled Yes/No"
FROM multi_app_documentation
INNER JOIN ip_visit_1 ON multi_app_documentation.nsma1_patnum = ip_visit_1.ipv1_num
INNER JOIN visit ON ip_visit_1.ipv1_num = visit.visit_id
WHERE multi_app_documentation.nsma1_code = 'DATE' 
    AND TO_DATE(multi_app_documentation.nsma1_ans, 'MMDDYYYY') = CURRENT_DATE
ORDER BY ip_visit_1.ipv1_lastname;

解决方案 #

1. 核心问题分析 #

报错是因为TO_DATE只能匹配单一格式，而字段里混了带/分隔符和纯数字的日期，直接用MMDDYYYY格式转换会把带分隔符的字符串解析成无效数字，导致报错。

2. 多格式日期转换逻辑 #

先把所有日期字符串清理成纯数字，再根据长度处理年份：

• 用REGEXP_REPLACE(nsma1_ans, '[^0-9]', '', 'g')去掉所有非数字字符
• 如果清理后长度是6（MMDDYY），补上前两位年份（默认20xx，需要兼容19xx的话可以加判断）
• 长度是8的话直接用MMDDYYYY格式转换

3. 修改后的完整查询 #

用CTE先预处理日期，过滤无效值，再关联查询，避免WHERE子句里直接转换报错：

WITH cleaned_dates AS (
    SELECT 
        md.*,
        -- 生成标准化的日期值
        CASE
            -- 只处理长度为6或8的纯数字日期
            WHEN LENGTH(REGEXP_REPLACE(md.nsma1_ans, '[^0-9]', '', 'g')) IN (6,8)
            THEN TO_DATE(
                CASE 
                    -- 处理6位日期（MMDDYY），补20前缀
                    WHEN LENGTH(REGEXP_REPLACE(md.nsma1_ans, '[^0-9]', '', 'g')) = 6
                    THEN SUBSTRING(REGEXP_REPLACE(md.nsma1_ans, '[^0-9]', '', 'g'), 1, 4) || '20' || SUBSTRING(REGEXP_REPLACE(md.nsma1_ans, '[^0-9]', '', 'g'), 5, 2)
                    -- 8位日期直接用
                    ELSE REGEXP_REPLACE(md.nsma1_ans, '[^0-9]', '', 'g')
                END,
                'MMDDYYYY'
            )
            -- 无效日期标记为NULL
            ELSE NULL 
        END AS procedure_date
    FROM multi_app_documentation md
    WHERE md.nsma1_code = 'DATE'
)
SELECT 
    -- 转成MMDDYYYY格式的字符串输出
    TO_CHAR(cd.procedure_date, 'MMDDYYYY') AS "Procedure Date",
    ' ' AS "Case Confirmation Number",
    ipv.ipv1_firstname AS "Patient First",
    ipv.ipv1_lastname AS "Patient Last",
    v.visit_sex AS "Patient Gender",
    TO_CHAR(v.visit_date_of_birth, 'MM/DD/YYYY') AS "DOB",
    v.visit_id AS "Account Number",
    v.visit_mr_num AS "MRN",
    ' ' AS "Module",
    ' ' AS "Signed off DT",
    -- 关联获取CRNA字段值
    (SELECT md.nsma1_ans FROM multi_app_documentation md WHERE md.nsma1_patnum = cd.nsma1_patnum AND md.nsma1_code = 'CRNA') AS "Primary CRNA",
    ' ' AS "Secondary CRNA",
    ' ' AS "Primary Anesthesiologist",
    ' ' AS "Secondary Anesthesiologist",
    ' ' AS "Canceled Yes/No"
FROM cleaned_dates cd
INNER JOIN ip_visit_1 ipv ON cd.nsma1_patnum = ipv.ipv1_num
INNER JOIN visit v ON ipv.ipv1_num = v.visit_id
-- 只匹配有效日期且等于当前日期的记录
WHERE cd.procedure_date = CURRENT_DATE
ORDER BY ipv.ipv1_lastname;

4. 兼容19xx年份的优化（可选） #

如果存在19xx的两位年份（比如01/23/99对应1999），可以调整年份判断逻辑：

TO_DATE(
    CASE 
        WHEN LENGTH(cleaned_str) = 6 THEN 
            CASE WHEN SUBSTRING(cleaned_str, 5, 2)::INT > EXTRACT(YEAR FROM CURRENT_DATE)::INT % 100 
                 THEN SUBSTRING(cleaned_str, 1, 4) || '19' || SUBSTRING(cleaned_str, 5, 2)
                 ELSE SUBSTRING(cleaned_str, 1, 4) || '20' || SUBSTRING(cleaned_str, 5, 2)
            END
        ELSE cleaned_str
    END,
    'MMDDYYYY'
)
WHERE cleaned_str = REGEXP_REPLACE(md.nsma1_ans, '[^0-9]', '', 'g')

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