SQL中使用DATE_ADD转换日期后如何正确获取年周格式（YYYY-WW）

问题根源 #

你遇到的问题来自两个核心错误：

1. DATE_ADD函数调用错误：多数SQL数据库要求明确指定时间间隔单位，你的写法缺少INTERVAL关键字和DAY单位，导致日期计算可能异常，进而影响后续周数计算。
2. DATE_FORMAT格式符使用错误：格式符必须以%开头，你直接用Y-u会被数据库解析为普通字符串而非格式指令，因此得到错误的周数结果。

修正后的SQL代码 #

以MySQL为例，正确的写法如下：

SELECT 
    order_date_key, 
    DATE_ADD('1900-01-01', INTERVAL order_date_key DAY) AS Year_month_day, 
    DATE_FORMAT(DATE_ADD('1900-01-01', INTERVAL order_date_key DAY), '%Y-%u') AS year_week
FROM your_table;

关键说明 #

• INTERVAL order_date_key DAY：明确告诉数据库要给起始日期加上指定天数，确保日期转换准确。
• %Y-%u：
  • %Y：输出4位完整年份
  • %u：输出周数（范围00-53，以周一作为一周的起始日），符合你期望的周数计算逻辑。

样本数据验证 #

针对你提供的样本值，修正后的代码会输出：

其他数据库适配（如PostgreSQL） #

如果使用PostgreSQL，语法略有不同，可使用TO_CHAR函数实现：

SELECT 
    order_date_key,
    DATE '1900-01-01' + order_date_key AS Year_month_day,
    TO_CHAR(DATE '1900-01-01' + order_date_key, 'YYYY-IW') AS year_week
FROM your_table;

其中IW代表ISO标准周数，与你期望的计算逻辑一致。

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