在Microsoft Fabric的Spark SQL中将日期字符串转为日期格式

问题核心 #

你当前转换失败的直接原因是to_timestamp的格式参数与Budget_Date的实际格式不匹配：

• Budget_Date的格式为dd MMM yyyy（例如30 May 2024、3 Dec 2022）
• 你传入的格式参数是dd/MM/yyyy，两者完全不匹配，导致转换返回NULL

解决方案 #

1. 修正日期转换的格式参数 #

根据你的需求，有两种合适的函数可选：

• 若仅需日期类型，使用to_date更贴合场景
• 若需保留时间戳类型，继续用to_timestamp，但格式参数必须改为dd MMM yyyy

2. 优化序数词处理逻辑 #

原CTE中多次嵌套REPLACE处理th/nd/st/rd的写法可以简化为regexp_replace，通过正则表达式一次性替换所有序数词后缀，代码更简洁高效。

3. 处理特殊值PLACEHOLDER #

避免对PLACEHOLDER执行日期转换，否则会触发转换错误，需通过条件判断跳过这类值。

修改后的完整代码 #

out = spark.sql(f"""
with cte1 as (
SELECT
    TITLE
    ,SYSTEMONLY as System_Only
    ,IF(SYSTEMONLY = "Yes",
        IF(len(TITLE) <= 28 AND TITLE LIKE '&Snapshot%',
            "PLACEHOLDER",
            -- 正则一次性替换所有序数词后缀
            regexp_replace(
                SUBSTRING(
                    TRIM(SUBSTRING(TITLE, INSTR(TITLE,' - ') + 3, LEN(TITLE))),
                    1,
                    len(TRIM(SUBSTRING(TITLE, INSTR(TITLE,' - ') + 3, LEN(TITLE)))) -6
                ),
                '(st|nd|rd|th)', ''
            )
        ),
        NULL
    ) as Budget_Date
FROM LakehouseName.TableName
WHERE SYSTEMONLY = 'Yes'
)
SELECT 
    TITLE, 
    System_Only, 
    Budget_Date,
    -- 仅对非PLACEHOLDER的值执行日期转换
    CASE 
        WHEN Budget_Date != 'PLACEHOLDER' THEN to_date(Budget_Date, 'dd MMM yyyy')
        ELSE NULL
    END AS Formatted_Date
FROM cte1
""")
display(out)

关键说明 #

• regexp_replace(..., '(st|nd|rd|th)', '')：通过正则匹配所有序数词后缀，一次性完成替换，比多次REPLACE更高效。
• to_date(Budget_Date, 'dd MMM yyyy')：MMM对应英文月份缩写（May、Dec等），Spark SQL可自动识别该格式。
• CASE语句：规避对PLACEHOLDER的无效转换，防止出现报错。

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