CREATE FUNCTION GetWorkDays
(
@dt_begin DATETIME , --
@dt_end DATETIME --
)
RETURNS INT
AS
BEGIN
DECLARE @workday INT ,
@i INT ,
@bz BIT ,
@dt DATETIME
IF @dt_begin > @dt_end
SELECT @bz = 1 ,
@dt = @dt_begin ,
@dt_begin = @dt_end ,
@dt_end = @dt
ELSE
SET @bz = 0
SELECT @i = DATEDIFF(Day, @dt_begin, @dt_end) + 1 ,
@workday = @i / 7 * 5 ,
@dt_begin = DATEADD(Day, @i / 7 * 7, @dt_begin)
WHILE @dt_begin <= @dt_end
BEGIN
SELECT @workday = CASE WHEN ( @@DATEFIRST + DATEPART(Weekday, @dt_begin) - 1 )
% 7 BETWEEN 1 AND 5 THEN @workday + 1
ELSE @workday
END ,
@dt_begin = @dt_begin + 1
END
RETURN(CASE WHEN @bz=1 THEN -@workday ELSE @workday END)
END
GO
--
SELECT dbo.GetWorkDays('2019-08-1', '2019-08-31') AS ' '