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')

posted @   每天进步多一点  阅读(9)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤
历史上的今天:
2016-12-31 SQL-LINQ-Lambda语法对照
2016-12-31 SQL 查询条件放在LEFT OUTER JOIN 的ON语句后与放在WHERE中的区别
点击右上角即可分享
微信分享提示