首先，Snowflake确实没有直接支持「年+周数+星期几」组合的日期构造函数，但我们可以通过DATE_TRUNC和DATEADD的组合来实现，完全覆盖你提到的跨年周、53周闰年这些特殊场景。

一、基础日期构造方法（以ISO周为例） #

假设你的周定义是ISO周（周一为一周起始，第1周是包含当年1月4日的那周），要构造(年, 周数, 星期几)对应的日期，可以按以下步骤：

1. 找到当年第N周的周一（ISO周的起始日）
2. 根据星期几的数值，加上对应天数（周一=1的话，加星期几-1天）

示例代码（对应你给出的2022年第13周周三） #

-- 定义参数
SET target_year = 2022;
SET target_week = 13;
SET target_day = 3; -- 周一=1，周日=7

-- 构造目标日期
WITH week_monday AS (
    -- 先获取当年第一个ISO周的周一，再偏移到目标周的周一
    SELECT DATEADD(WEEK, $target_week - 1, DATE_TRUNC('ISO_WEEK', DATE_FROM_PARTS($target_year, 1, 1))) AS start_of_week
)
SELECT DATEADD(DAY, $target_day - 1, start_of_week) AS target_date
FROM week_monday;

执行后会得到2022-03-30，完全符合你的预期。

二、适配dimDate表的weekToDate和去年对比标记 #

针对你需要在dimDate表中生成weekToDate和weekToDatePrevYear的需求，我们可以把上述逻辑整合到表的更新或计算中，同时处理53周的特殊规则（当年53周时，去年对比用第52周）。

1. 生成weekToDate标记 #

weekToDate表示该日期属于本周从周一到当日的区间，用布尔值标记的话：

ALTER TABLE dimDate ADD COLUMN IF NOT EXISTS weekToDate BOOLEAN;

UPDATE dimDate
SET weekToDate = CASE
    -- 判断当前日期是否在本周周一到自身之间
    WHEN date_key BETWEEN DATE_TRUNC('ISO_WEEK', date_key) AND date_key THEN TRUE
    ELSE FALSE
END;

2. 生成weekToDatePrevYear标记 #

这个标记需要匹配「去年同周数（最多52周）的对应星期几区间」，核心逻辑是先构造出去年目标周的周一，再计算到对应星期几的日期，然后判断当前日期是否在这个区间内：

ALTER TABLE dimDate ADD COLUMN IF NOT EXISTS weekToDatePrevYear BOOLEAN;

UPDATE dimDate
SET weekToDatePrevYear = CASE
    WHEN date_key BETWEEN
        -- 构造去年目标周的周一：取当年周数和52的最小值，偏移到去年对应周的周一
        DATEADD(WEEK, LEAST(WEEKOFYEARISO(date_key), 52) - 1, DATE_TRUNC('ISO_WEEK', DATE_FROM_PARTS(YEAR(date_key)-1, 1, 1)))
        AND
        -- 构造去年目标周的对应星期几日期
        DATEADD(DAY, DAYOFWEEKISO(date_key)-1, DATEADD(WEEK, LEAST(WEEKOFYEARISO(date_key), 52) - 1, DATE_TRUNC('ISO_WEEK', DATE_FROM_PARTS(YEAR(date_key)-1, 1, 1))))
    THEN TRUE
    ELSE FALSE
END;

三、注意事项 #

• 周定义调整：如果你的业务周不是ISO周（比如以周日为起始），只需把ISO_WEEK替换为WEEK，同时调整星期几的数值映射（Snowflake的DAYOFWEEK函数默认周日=1，周六=7）。
• 跨年周处理：DATE_TRUNC('ISO_WEEK', ...)会自动识别跨年度的周归属，比如2022年12月31日属于2023年第1周，用该函数会正确定位到2022年12月26日（该周的周一）。
• 53周场景：通过LEAST(WEEKOFYEARISO(date_key), 52)确保不会引用去年不存在的53周（只有部分年份有53个ISO周）。

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