25 Okt 2013

DateDiff and DateAdd Excluding Weekends in SQL Script

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;
 
END

Enjoy, and hope it helps! Winking smile