# sql计算工作日函数

1、建节假日表

CREATE TABLE [Holiday] (
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[BeginDate] [datetime] NOT NULL ,
[EndDate] [datetime] NOT NULL ,
[AddUser] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
CONSTRAINT [PK_Holiday] PRIMARY KEY  CLUSTERED
(
[Id]
)  ON [PRIMARY]
) ON [PRIMARY]
GO

2、计算工作日函数

CREATE function [dbo].[WorkDay]
(
@beginday   datetime,
@endday   datetime
)

returns int
AS
begin
--set   datefirst   1
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
if(@id=0)
select   @caldays=@caldays+1
end
end
return   @caldays
end

3、推算指定日期前N个工作日的日期函数

CREATE function [dbo].[BeginDate]
(
@workday  int   ,
@endday   datetime
)

returns datetime
AS
begin
declare  @beginday   datetime
declare  @id   int
select @beginday=@endday
while  DATEDIFF(d, @beginday,@endday)<@workday
begin
if  datepart(dw,@beginday)>1 and datepart(dw,@beginday)<7
begin
if(@id>0)
select   @workday=@workday+1
end
else
begin
select   @workday=@workday+1
end
end
if(datepart(dw,@beginday)=2)
begin