Labels

Saturday, December 31, 2011

Function to return no of business days between the provided time range

CREATE FUNCTION ufnGetBusinessDays (@DateStart DATETIME, @DateEnd   DATETIME)
RETURNS INT
AS
  BEGIN
    IF (@DateStart IS NULL OR @DateEnd IS NULL)  RETURN (0)
    DECLARE  @i INT = 0; 
    WHILE (@DateStart <= @DateEnd)
      BEGIN
        SET @i = @i + CASE
                        WHEN datepart(dw,@DateStart) BETWEEN 2 AND 6 THEN 1
                        ELSE 0
                      END 
        SET @DateStart = @DateStart + 1
      END  -- while 
    RETURN (@i)
  END -- function

GO

SELECT dbo.ufnGetBusinessDays('2016-01-01','2016-12-31')

No comments:

Post a Comment