sqlServer利用递归查询------将日期范围转换为日期表

-- 创建日期表
CREATE TABLE #DateTable
(
    TheDate DATE NOT NULL PRIMARY KEY
);
 
-- 插入日期序列
DECLARE @StartDate DATE = '2021-01-01', @EndDate DATE = '2021-12-31';
 
WITH DateSequence(Date) AS
(
    SELECT CAST(@StartDate AS DATE) AS Date
    UNION ALL
    SELECT DATEADD(DAY, 1, Date)
    FROM DateSequence
    WHERE DATEADD(DAY, 1, Date) <= @EndDate
)
INSERT INTO d#DateTable(TheDate)
SELECT Date
FROM DateSequence
OPTION (MAXRECURSION 0); -- 移除递归限制

select *
from DateTable

---------------

ALTER FUNCTION [dbo].[fn_GenerateDates]
(
    @StartDate DATETIME, 
    @EndDate DATETIME
)
 
RETURNS @Output TABLE 
(
      Value NVARCHAR(4000)
)
 
AS
 
BEGIN
 
    INSERT INTO @Output
    
    SELECT TOP (DATEDIFF(Day, @StartDate, @EndDate)+1) 
        CONVERT(VARCHAR(10), YEAR(DATEADD(Day, number, @StartDate))) + '-' + DATENAME(MONTH, DATEADD(Day, number, @StartDate)) + '-' + DATENAME(Day, DATEADD(Day, number, @StartDate)) AS Months
        FROM [master].dbo.spt_values 
        WHERE [type] = N'P' 
        ORDER BY number
 
    RETURN
END
 

 
select Value from [dbo].fn_GenerateDates('2001-02-01','2001-02-15')
 
select Value from [dbo].fn_GenerateDates('1951-01-01','2024-12-31')