时间段天数用sql排除节假日

首先创建[holiday]表记录国家法定假日

CREATE TABLE [dbo].[holiday](
	[Id] [int] IDENTITY(1,1) identity(1,1) NOT NULL,
	[HoliDate] [datetime] NULL,
	[Description] [varchar](100)
)

 

然后写一个标量值函数

create function [dbo].[dateArithmetic](   
    @stratDate   datetime, --开始时间  
    @endDate   datetime --结束时间  
)  
returns int   
as
begin   
    DECLARE @jqday varchar(20)  
    DECLARE @days int  
    DECLARE @weekday varchar(20)  
      
    --获取总天数  
    set @days=DATEDIFF(day,@stratDate,@endDate)    
   
    select @jqday=count(*) from holiday where HoliDate >= @stratDate and HoliDate<=@endDate  
    while @stratDate<=@endDate  
    begin  
          
       
       if exists (select count(*) from holiday where HoliDate=@stratDate)
        begin
        select @weekday=datename(weekday,@stratDate)  
          
        if @weekday='星期六' or @weekday='星期日'  
            set @jqday=@jqday+1  
        set @stratDate=dateadd(day,1,@stratDate)  
        end
       else
       begin
        select @weekday=datename(weekday,@stratDate)  
          
        if @weekday='星期六' or @weekday='星期日'  
            set @jqday=@jqday-1  
        set @stratDate=dateadd(day,1,@stratDate)  
       end
    end  
      
    set @days=@days-@jqday  
    --print @weekday  
    return(@days)  
end