如何在SQL Server中生成指定日期范围内的日期列表？

嘿，我来分享几个在SQL Server里生成指定日期范围列表的实用方案，每个都配了具体示例，你可以根据自己的场景选最合适的：

方法1：递归CTE（最灵活，无需额外依赖） #

这是最常用的方法，不需要提前创建任何对象，直接写CTE就能生成日期序列。核心思路是先定义起始日期，然后递归地每天加1天，直到达到结束日期。

示例：生成2024-01-01到2024-01-10的日期列表 #

DECLARE @StartDate DATE = '2024-01-01',
        @EndDate DATE = '2024-01-10';

WITH DateList AS (
    SELECT @StartDate AS DateValue
    UNION ALL
    SELECT DATEADD(DAY, 1, DateValue)
    FROM DateList
    WHERE DateValue < @EndDate
)
SELECT DateValue
FROM DateList
OPTION (MAXRECURSION 0); -- 当日期范围超过100天时必须加这个，默认递归上限是100

优缺点：

• ✅ 无需提前准备，随写随用
• ❌ 日期范围特别大（比如几年）时，性能不如基于数字表的方法

方法2：利用系统数字表（高性能，适合大范围） #

SQL Server的系统表master.dbo.spt_values里包含了现成的数字序列，我们可以用它来快速生成日期列表，比递归CTE高效得多，尤其适合大跨度的日期范围。

示例：生成2024年全年的日期列表 #

DECLARE @StartDate DATE = '2024-01-01',
        @EndDate DATE = '2024-12-31';

SELECT DATEADD(DAY, number, @StartDate) AS DateValue
FROM master.dbo.spt_values
WHERE type = 'P' -- 筛选出正整数序列
AND number <= DATEDIFF(DAY, @StartDate, @EndDate);

优缺点：

• ✅ 性能优异，大范围内速度快
• ❌ 依赖系统表，部分严格权限的环境可能无法访问

方法3：自定义日历表（长期复用，扩展能力强） #

如果你的业务经常需要处理日期相关的查询，建议提前创建一个专门的日历表，不仅能生成日期范围，还能预先存储年份、月份、星期几、是否工作日等维度信息，用起来特别方便。

步骤1：创建日历表 #

CREATE TABLE dbo.Calendar (
    DateValue DATE PRIMARY KEY,
    Year INT,
    Month INT,
    Day INT,
    WeekDay INT, -- 1=周日, 2=周一...7=周六（取决于SQL Server的语言设置）
    IsWorkday BIT -- 自定义标记是否为工作日
);

步骤2：填充日历表（用数字表快速插入10年的日期） #

DECLARE @StartDate DATE = '2020-01-01',
        @EndDate DATE = '2029-12-31';

INSERT INTO dbo.Calendar (DateValue, Year, Month, Day, WeekDay, IsWorkday)
SELECT 
    DATEADD(DAY, number, @StartDate) AS DateValue,
    YEAR(DATEADD(DAY, number, @StartDate)) AS Year,
    MONTH(DATEADD(DAY, number, @StartDate)) AS Month,
    DAY(DATEADD(DAY, number, @StartDate)) AS Day,
    DATEPART(WEEKDAY, DATEADD(DAY, number, @StartDate)) AS WeekDay,
    -- 这里简单标记周一到周五为工作日，你可以根据节假日调整
    CASE WHEN DATEPART(WEEKDAY, DATEADD(DAY, number, @StartDate)) IN (2,3,4,5,6) THEN 1 ELSE 0 END AS IsWorkday
FROM master.dbo.spt_values
WHERE type = 'P'
AND number <= DATEDIFF(DAY, @StartDate, @EndDate);

步骤3：查询指定范围的日期（比如2024年2月的工作日） #

SELECT DateValue
FROM dbo.Calendar
WHERE DateValue BETWEEN '2024-02-01' AND '2024-02-29'
AND IsWorkday = 1;

优缺点：

• ✅ 查询速度极快，支持复杂的日期维度筛选
• ✅ 一次创建长期复用，适合频繁的日期需求
• ❌ 需要提前维护，比如每年更新节假日信息

额外技巧：生成按周/月间隔的日期列表 #

如果不是每天生成，而是按周或月间隔，只需要修改DATEADD的参数就行。比如生成2024年每个月的第一天：

DECLARE @StartDate DATE = '2024-01-01',
        @EndDate DATE = '2024-12-31';

WITH MonthStarts AS (
    SELECT @StartDate AS DateValue
    UNION ALL
    SELECT DATEADD(MONTH, 1, DateValue)
    FROM MonthStarts
    WHERE DateValue < @EndDate
)
SELECT DateValue
FROM MonthStarts
OPTION (MAXRECURSION 0);

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