SQL如何查找上一年最接近的同星期日期？含同月限制需求

一、通用场景：找上一年最接近的同星期几日期 #

核心思路是生成上一年中目标日期前后的两个同星期几候选日期，再筛选出与原日期间隔最小的那个。

SQL Server 示例代码 #

DECLARE @target_date DATE = '2023-06-17';
DECLARE @target_dow INT = DATEPART(dw, @target_date);
DECLARE @prev_year_base DATE = DATEADD(YEAR, -1, @target_date);

-- 生成两个候选同星期几日期
WITH candidate_dates AS (
    SELECT 
        DATEADD(DAY, (@target_dow - DATEPART(dw, @prev_year_base)) % 7, @prev_year_base) AS candidate
    UNION ALL
    SELECT 
        DATEADD(DAY, (@target_dow - DATEPART(dw, @prev_year_base)) % 7 - 7, @prev_year_base) AS candidate
)
-- 筛选上一年日期并按间隔排序取最近
SELECT TOP 1 candidate AS closest_same_dow_date
FROM candidate_dates
WHERE candidate >= DATEADD(YEAR, -1, DATEFROMPARTS(YEAR(@target_date), 1, 1))
  AND candidate < DATEFROMPARTS(YEAR(@target_date), 1, 1)
ORDER BY ABS(DATEDIFF(DAY, @target_date, candidate)) ASC;

MySQL 示例代码 #

SET @target_date = '2023-06-17';
SET @target_dow = DAYOFWEEK(@target_date);
SET @prev_year_base = DATE_SUB(@target_date, INTERVAL 1 YEAR);

WITH candidate_dates AS (
    SELECT 
        DATE_ADD(@prev_year_base, INTERVAL (@target_dow - DAYOFWEEK(@prev_year_base)) % 7 DAY) AS candidate
    UNION ALL
    SELECT 
        DATE_ADD(@prev_year_base, INTERVAL (@target_dow - DAYOFWEEK(@prev_year_base)) % 7 - 7 DAY) AS candidate
)
SELECT candidate AS closest_same_dow_date
FROM candidate_dates
WHERE candidate >= DATE_FORMAT(DATE_SUB(@target_date, INTERVAL 1 YEAR), '%Y-%m-01')
  AND candidate < DATE_FORMAT(@target_date, '%Y-%m-01')
ORDER BY ABS(DATEDIFF(@target_date, candidate)) ASC
LIMIT 1;

二、限制在同一月份内查找 #

只需在筛选条件中加入月份匹配的逻辑，确保候选日期属于上一年的同一月份。

SQL Server 示例代码 #

DECLARE @target_date DATE = '2023-06-17';
DECLARE @target_dow INT = DATEPART(dw, @target_date);
DECLARE @target_month INT = MONTH(@target_date);
DECLARE @prev_year_base DATE = DATEADD(YEAR, -1, @target_date);

WITH candidate_dates AS (
    SELECT 
        DATEADD(DAY, (@target_dow - DATEPART(dw, @prev_year_base)) % 7, @prev_year_base) AS candidate
    UNION ALL
    SELECT 
        DATEADD(DAY, (@target_dow - DATEPART(dw, @prev_year_base)) % 7 - 7, @prev_year_base) AS candidate
)
SELECT TOP 1 candidate AS closest_same_dow_same_month_date
FROM candidate_dates
WHERE candidate >= DATEFROMPARTS(YEAR(@target_date)-1, @target_month, 1)
  AND candidate < DATEFROMPARTS(YEAR(@target_date), @target_month, 1)
ORDER BY ABS(DATEDIFF(DAY, @target_date, candidate)) ASC;

关键注意事项 #

• 星期起始差异：不同数据库对星期几的编号规则不同（如SQL Server默认周日=1，MySQL默认周日=1，PostgreSQL默认周一=0），若需统一逻辑，可改用星期名称对比（如DATENAME(dw, date)）。
• 边界情况处理：若上一年同一月份不存在同星期几日期（极端情况），需根据业务需求补充兜底逻辑（如返回NULL或当月最近的其他星期几）。

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