sqlserver计算两个日期之间的工作日(剔除周末及法定节假日)

ichuany

已于 2022-07-05 08:24:41 修改

阅读量2.1k

点赞数 2

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

于 2022-07-05 08:23:25 首次发布

原文链接：https://blog.csdn.net/wyzlwyzl/article/details/19565537

版权

数据库专栏收录该内容

1 篇文章 1 订阅

订阅专栏

sqlserver计算两个日期之间的工作日(剔除周末及法定节假日)

create function [dbo].[WorkDay]
(
@beginday   datetime,
@endday   datetime
)
returns int
AS
begin
	declare  @caldays   int  
	declare  @id   int
	select   @caldays=0	
 
	while  DATEDIFF(d, @beginday,@endday)>=0
	begin
		if  datepart(dw,@beginday)>1 and datepart(dw,@beginday)<7
			begin
				SELECT @id=count(*) from Holiday where @beginday between begin_date and DATEADD(s,-1,DATEADD(day,1,end_date)) and exchange=0
				if(@id=0)  
					select  @caldays=@caldays+1
			end
		else
			begin
				SELECT @id=count(*) from Holiday where @beginday between begin_date and DATEADD(s,-1,DATEADD(day,1,end_date)) and exchange=1
				if(@id>0)
					select  @caldays=@caldays+1
			end
		select  @beginday=dateadd(day,1,@beginday)
	end  
	return   @caldays
end

	

--select datepart(dw,'2022-02-06')

--select [dbo].[WorkDay]('2022-04-23','2022-04-24')
----节假日
insert into Holiday(fname,begin_date,end_date,exchange) values ('元旦','2022-01-01','2022-01-03',0);
insert into Holiday(fname,begin_date,end_date,exchange) values ('春节','2022-01-31','2022-02-06',0);
insert into Holiday(fname,begin_date,end_date,exchange) values ('清明节','2022-04-03','2022-04-05',0);
insert into Holiday(fname,begin_date,end_date,exchange) values ('劳动节','2022-04-30','2022-05-04',0);
insert into Holiday(fname,begin_date,end_date,exchange) values ('端午节','2022-06-03','2022-06-05',0);
insert into Holiday(fname,begin_date,end_date,exchange) values ('中秋节','2022-09-10','2022-09-12',0);
insert into Holiday(fname,begin_date,end_date,exchange) values ('国庆节','2022-10-01','2022-10-07',0);
----上班补充
insert into Holiday(fname,begin_date,end_date,exchange) values ('春节调休','2022-01-29','2022-01-30',1);--
insert into Holiday(fname,begin_date,end_date,exchange) values ('清明节调休','2022-04-02','2022-04-02',1);--
insert into Holiday(fname,begin_date,end_date,exchange) values ('劳动节调休','2022-04-24','2022-04-24',1);
insert into Holiday(fname,begin_date,end_date,exchange) values ('劳动节调休','2022-05-07','2022-05-07',1);
insert into Holiday(fname,begin_date,end_date,exchange) values ('国庆节调休','2022-10-08','2022-10-09',1);