-- =============================================
-- Author: LJB
-- Create date:2019年3月19日14:29:23
-- Description: 获取工作日
-- =============================================
CREATE FUNCTION [dbo].[f_GetWorkday](@bdate DATETIME, @edate DATETIME,@workDayEnum INT)
RETURNS INT
AS
BEGIN
DECLARE @workdays INT;
DECLARE @saturdayCount INT;
IF(@edate <= CONVERT(datetime,'1900-01-01'))
SET @edate = GETDATE();
IF(@workDayEnum = 1) --周六、周日休息
BEGIN
SET @workdays = 0;
END
ELSE IF(@workDayEnum = 2) --周六、周日不休息
BEGIN
SET @workdays = DATEDIFF(day,@bdate,@edate) + 1;
RETURN @workdays;
END
ELSE --周六不休息
BEGIN
SET @workdays = datediff(wk,@bdate,@edate);--两个时间内有多少个周六
END
--一周5个工作日计算
while DATEDIFF(d, @bdate, @edate) >= 0
begin
if datepart(dw,@bdate) > 1 and datepart(dw,@bdate) < 7
begin
select @workdays=@workdays+1
end
select @bdate=dateadd(day,1,@bdate)
end
RETURN @workdays;
END
GO