如何在SQL中从时间戳获取以周日为起始的周起始日期

需求说明：
拥有包含大量时间戳的数据源，需要从时间戳中提取以周日为起始的周起始日期。例如，将Received Date列中的2022-11-23 00:00:00.0转换为2022-11-20（2022-11-23为周三，对应周日起始周的第一天是2022-11-20）。

以下是实现该需求的SQL查询语句：

SELECT
    CASE
        WHEN sorttype = 'sortable' THEN 'Sort'
        WHEN sorttype = 'SORTABLE' THEN 'Sort'
        ELSE 'Non Sort'
    END AS "Sort Type",
    DATEADD(week, DATEDIFF(week, -1, (order_day + podays_received)), -1) AS "Week Start",
    (order_day + podays_received) AS "Received Date",
    pg_rollup AS "Product Family",
    gl,
    quantity_unpacked AS "Quantity Received"
FROM 
    sba.sba_req_po_received
WHERE
    source = 'OPPORTUNITYBUYING'
    AND country_code = 'US'
    AND NOT buy_type_name = 'Offcycle'
LIMIT 100;

核心逻辑说明 #

提取周起始日期的关键在于DATEADD(week, DATEDIFF(week, -1, 目标日期), -1)：

• DATEDIFF(week, -1, 目标日期)：计算从基准日期（-1对应SQL中的1900-01-01，该日期为周一，通过偏移基准实现周日作为周起始）到目标日期的周数差。
• DATEADD(week, 周数差, -1)：基于基准日期加上周数差后，再偏移-1天，得到目标日期所在周的周日起始日期。

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