根据开始和结束时间计算工作日，排除指定节假日和双休日

1.建立一个数据库表结构(HOLIDAY)保存设定好的节假日，包括三个字段ID,DATE,DESCRIPTION

2.建立存储过程dateArithmetic：

--计算节假日alter function dateArithmetic( @stratDate   datetime, --开始时间@endDate   datetime --结束时间)returns int as begin DECLARE @jqday varchar(20)DECLARE @days intDECLARE @weekday varchar(20)--获取总天数set @days=DATEDIFF(day,@stratDate,@endDate)  --set language N'Simplified Chinese'--select @jqday=count(SH00) from HOLIDAY where sh01 between @stratDate and @endDateselect @jqday=count(SH00) from HOLIDAY where sh01 >= @stratDate and sh01<=@endDatewhile @stratDate<=@endDatebegin--设为简体中文select @weekday=datename(weekday,@stratDate)if @weekday='星期六' or @weekday='星期日'set @jqday=@jqday+1set @stratDate=dateadd(day,1,@stratDate)endset @days=@days-@jqday--print @weekdayreturn(@days)end

?

3.调取存储过程：select dbo.dateArithmetic(开始时间,结束时间)

?

?