如何在Snowflake中实现MySQL STR_TO_DATE(%X%V%W)的等效功能？

要实现MySQL中STR_TO_DATE(CONCAT('YYYYWW',' Sunday'), '%X%V %W')的功能（即根据年份、周日起始的周数，返回该周的周日日期），Snowflake没有直接对应的内置函数，但可以通过自定义逻辑或UDF来实现。

核心逻辑说明 #

MySQL的%X%V %W参数组合对应：

• %X：周所属的4位年份
• %V：以周日为起始的周数
• %W：指定周起始日为周日，最终返回该周的周日日期

在Snowflake中可按以下步骤计算：

1. 从输入字符串中提取年份和周数（取前6位字符，格式为YYYYWW）
2. 计算该年的第一个周日日期
3. 基于第一个周日，加上(周数-1)*7天，得到目标周的周日日期

实现方案 #

1. 直接SQL表达式 #

针对单个输入，可直接使用以下表达式：

SELECT 
    DATEADD(
        day,
        (TO_NUMBER(RIGHT(LEFT('202313 Sunday', 6), 2)) - 1) * 7,
        DATEADD(
            day,
            CASE WHEN DAYOFWEEK(TO_DATE(LEFT('202313 Sunday', 4) || '-01-01'), 1) = 1 THEN 0 ELSE 7 - DAYOFWEEK(TO_DATE(LEFT('202313 Sunday', 4) || '-01-01'), 1) + 1 END,
            TO_DATE(LEFT('202313 Sunday', 4) || '-01-01')
        )
    ) AS target_date;

2. 自定义UDF（推荐，方便复用） #

创建一个自定义函数封装上述逻辑：

CREATE OR REPLACE FUNCTION STR_TO_DATE_MYSQL(input_str VARCHAR)
RETURNS DATE
AS $$
DECLARE
    year_week_str VARCHAR := LEFT(input_str, 6); -- 提取YYYYWW部分
    year_start DATE := TO_DATE(LEFT(year_week_str, 4) || '-01-01'); -- 当年1月1日
    first_sunday DATE := DATEADD(
        day,
        -- 计算当年第一个周日的日期
        CASE WHEN DAYOFWEEK(year_start, 1) = 1 THEN 0 ELSE 7 - DAYOFWEEK(year_start, 1) + 1 END,
        year_start
    );
    week_num INT := TO_NUMBER(RIGHT(year_week_str, 2)); -- 提取周数
BEGIN
    -- 计算目标周的周日日期
    RETURN DATEADD(day, (week_num - 1) * 7, first_sunday);
END;
$$;

3. 测试验证 #

调用UDF测试你的示例：

SELECT STR_TO_DATE_MYSQL('201439 Sunday'); -- 输出：2014-09-28
SELECT STR_TO_DATE_MYSQL('201440 Sunday'); -- 输出：2014-10-05
SELECT STR_TO_DATE_MYSQL('202313 Sunday'); -- 输出：2023-03-26

注意事项 #

• DAYOFWEEK函数中指定week_start=1，确保以周日为周起始日，不受账户默认设置影响
• 输入字符串需符合YYYYWW 星期几格式，函数会自动提取前6位的年份周数部分

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