-- 创建日期表
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')