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 #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

eg: 

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