SQL实现按当前日期动态获取当月/次月自定义日的查询方案

问题背景 #

现有一张表（假设表名为target_table），结构及数据如下：

需要根据当前日期（GETDATE()）动态生成结果日期，规则如下：

• 当当前日期的日部分小于表中Day字段值时，生成当月对应Day的日期；
• 当当前日期的日部分大于等于表中Day字段值时，生成次月对应Day的日期；
  查询需适配任意年月。

解决方案SQL #

SELECT
    CAST(
        DATEADD(
            MONTH,
            CASE WHEN DAY(GETDATE()) >= t.Day THEN 1 ELSE 0 END,
            DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), t.Day)
        ) AS DATETIME
    ) AS result
FROM target_table t;

逻辑说明 #

1. DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), t.Day)：先构造出当月对应Day的基础日期；
2. CASE WHEN DAY(GETDATE()) >= t.Day THEN 1 ELSE 0 END：判断当前日期的日部分是否大于等于表中Day值，决定是否需要将月份加1；
3. DATEADD(MONTH, ...)：根据判断结果调整月份，最后转为DATETIME类型得到目标结果。

验证场景 #

• 场景1：当前日期为2022-9-5，DAY(GETDATE())=5，所有表中Day值均大于5，生成当月对应日期，结果符合预期；
• 场景2：当前日期为2022-9-16，DAY(GETDATE())=16，表中Day=8、14小于16，生成次月（10月）对应日期；Day=22、29大于等于16，保留当月日期，结果符合预期。

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