SQL Server获取连续区间的日期

个人理解的方法有三种

1. 通过系统表master..spt_values获取
2. 用while循环获取
3. 游标获取

 

方法一：通过系统表master..spt_values获取

1、获取连续的日

-- 获取连续区间的日期
declare @starttime date = '2019-03-08', -- 开始时间
        @endtime   date = '2019-03-18' -- 结束时间

select convert(nvarchar(10),dateadd(day,number,@starttime),120) as daytime
  from master..spt_values  
 where type = 'p' 
   and number <= datediff(day,@starttime,@endtime)

 

 

 
2、获取连续的月

-- 获取连续区间的月份
declare @starttime date = '2019-03-08', -- 开始时间
        @endtime   date = '2019-12-28' -- 结束时间

select convert(varchar(7),dateadd(month,number,@starttime),120) as monthtime
  from master..spt_values with (nolock) 
 where type='p' 
   and number <= datediff(month,@starttime,@endtime)

 

3、获取连续的年

-- 获取连续区间的年份
declare @starttime date = '2019-03-08', -- 开始时间
        @endtime   date = '2020-03-08' -- 结束时间

select convert(varchar(4),dateadd(year,number,@starttime),120) as yeartime
  from master..spt_values with (nolock) 
 where type='p' 
   and number <= datediff(year,@starttime,@endtime)

 

master..spt_values原理：

通过number来实现年月日的加减，因为number值最大是2047，所以只能连续加2047。

如图：结束时间是'2026-03-28'，但我们降序可以看到，从2019-03-08只能增加连续2047天，如此2024-10-24之后的日期就没有了。

所以如果连续区间超过了2048，则此方法不适用，此时可以用以下方法二来实现

 

方法二：用while循环获取

1、获取连续的日

-- 连续获取天
declare @starttime date = '2019-03-08', -- 开始时间
        @endtime   date = '2019-03-18' -- 结束时间

-- 创建临时表#datetime存储日期
create table #datetime
(
    daytime date
);

-- 循环获取日期插入临时表
while @starttime <= @endtime
begin
    insert into #datetime (daytime)
    values (@starttime);
    set @starttime = dateadd(day, 1, @starttime);
end;

select daytime from #datetime;

-- 删除临时表
drop table #datetime;

 

 

2、获取连续的月

-- 连续获取的月
declare @starttime date = '2019-03-08', -- 开始时间
        @endtime   date = '2019-12-28' -- 结束时间

-- 创建临时表#monthtime存储日期
create table #monthtime
(
    monthtime varchar(7)
);



-- 循环获取日期插入临时表
while @starttime <= @endtime
begin
    insert into #monthtime (monthtime)
    values (convert(varchar(7),@starttime));
    set @starttime = dateadd(month, 1, @starttime);
end;

select monthtime from #monthtime;

-- 删除临时表
drop table #monthtime;

 

3、获取连续的年

-- 连续获取的月
declare @starttime date = '2019-03-08', -- 开始时间
        @endtime   date = '2025-03-28' -- 结束时间

-- 创建临时表#yeartime存储日期
create table #yeartime
(
    yeartime varchar(4)
);



-- 循环获取日期插入临时表
while @starttime <= @endtime
begin
    insert into #yeartime (yeartime)
    values (convert(varchar(4),@starttime));
    set @starttime = dateadd(year, 1, @starttime);
end;

select yeartime from #yeartime;

-- 删除临时表
drop table #yeartime;

 

方法三：用游标获取，原理和while循环相似，此处不再展示

 

如果有什么好的方法和建议，欢迎大家来指点，谢谢！