Have you ever wonder how to count date differences and add date, but excluding weekends (Saturday and Sunday)? I stumbled on these 2 functions, which I prefer best to do the operation.
CREATE FUNCTION [VARS].[FN_DateAddBusinessDay]
(
@InputDate DATETIME = NULL,
@InputDays INT
)
RETURNS DATETIME
AS
BEGIN
-- Declare the return variable here
DECLARE @OutputDateTime DATETIME
DECLARE @date DATETIME
DECLARE @days INT
DECLARE @weeks INT
DECLARE @i INT = 0
-- Add the T-SQL statements to compute the return value here
SET @date = @InputDate
SET @days = @InputDays
IF @days >= 1
BEGIN
SET @days = @days - 1
END
IF DATENAME(dw, @date) = 'Saturday'
BEGIN SET @i = 2 END
ELSE IF DATENAME(dw, @date) = 'Sunday'
BEGIN SET @i = 1 END
SET @date = DATEADD(d, @i, @date)
IF (@days < 0)
BEGIN
IF DATEPART(dw, @date) = 1
SET @date = DATEADD(d, -1, @date)
SET @weeks = (datepart (dw, @date) + @days - 6)/5
END
ELSE
BEGIN
IF DATEPART(dw, @date) = 7
SET @date = DATEADD(d, 1, @date)
SET @weeks = (DATEPART(dw, @date) + @days - 2)/5
END -- Return the result of the function
SET @OutputDateTime = DATEADD(d, @days + (@weeks * 2), @date);
RETURN @OutputDateTime;
END
GO
CREATE FUNCTION [VARS].[FN_DateDiffBusinessDay]
(
@InputStartDate DATETIME = NULL,
@InputEndDate DATETIME = NULL
)
RETURNS INT
AS
BEGIN
-- Declare the return variable here
DECLARE @OutputDays INT
DECLARE @date DATETIME
DECLARE @days INT
DECLARE @weeks INT
-- Add the T-SQL statements to compute the return value here
IF DATEDIFF(DD, @InputStartDate, @InputEndDate) >= 0
BEGIN
SET @OutputDays = (SELECT
(DATEDIFF(dd, @InputStartDate, @InputEndDate) + 1)
-(DATEDIFF(wk, @InputStartDate, @InputEndDate) * 2)
-(CASE WHEN DATENAME(dw, @InputStartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, @InputEndDate) = 'Saturday' THEN 1 ELSE 0 END))
END
ELSE
BEGIN
SET @OutputDays = 0
END
RETURN @OutputDays;
ENDEnjoy, and hope it helps!
