SQLServer自动生成月份范围

月下狂心

已于 2022-07-13 09:10:39 修改

阅读量408

点赞数

分类专栏：数据库分析文章标签： sqlserver

于 2022-07-13 08:58:27 首次发布

本文链接：https://blog.csdn.net/xy975357833/article/details/125756150

版权

数据库分析专栏收录该内容

1 篇文章 0 订阅

订阅专栏

declare @watch datetime,@elapsedTime as int,@zxsj nvarchar(1000),@i int ,@count int
 declare @kssj datetime,@jssj datetime
select @i=0,@kssj='2021-01-01',@jssj='2022-07-01'--不包含结束时间
select @count=count(*) from (select dateadd(month,number,@kssj) kssj from master..spt_values where type='P') a where kssj<@jssj;
declare cur cursor for 
select kssj,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,kssj)+1,0)) jssj from	
	(select dateadd(month,number,@kssj) kssj from master..spt_values where type='P') a where kssj<@jssj
open cur;
fetch next from cur into @kssj,@jssj;
while @@FETCH_STATUS=0
begin
	select @watch = getdate(),@i=@i+1;
	--插入需要查询的语句begin
    select @kssj,@jssj;
	--插入需要查询的语句end
    select @elapsedTime=datediff(ms,@watch,getdate())
	select @zxsj='ID:'+ cast(@i as nvarchar(50)) +'/'+cast(@count as nvarchar(50))+' QueryRange:' + convert(nvarchar(10),@kssj,121) + ' ~ ' + convert(nvarchar(10),@jssj,121) +' elapsedTime: '+ convert(varchar(100),dateadd(ms,@elapsedTime,1),14) 
	print @zxsj;
fetch next from cur into @kssj,@jssj;
end
close cur;
deallocate cur;