search
Carter Cole LinkedInCarters Twitter PageCarter Cole on Facebook Carter Coles RSS

Thursday, February 24, 2011

Calculating business days between dates in SQL (a datediff function without weekends)

Im having to kinda roll my own simplest cms in the world for a customers contact script and they want to know how many business days are between the customer contact and when they responded. After some digging and optimization this is the DATEDIFF sql function I came up with that excludes weekends...

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION BDATEDIFF
(
 @startdate as DATETIME,
 @enddate as DATETIME
)
RETURNS INT
AS
BEGIN
 DECLARE @res int
 
SET @res = (DATEDIFF(dd, @startdate, @enddate) + 1)
  -(DATEDIFF(wk, @startdate, @enddate) * 2)
  -(CASE WHEN DATEPART(dw, @startdate) = 1 THEN 1 ELSE 0 END)
  -(CASE WHEN DATEPART(dw, @enddate) = 7 THEN 1 ELSE 0 END)
  
 RETURN @res

END
GO

Ill add holiday support if i have time but it shouldn't be tough at all just select count of holidays that fall between the dates and subtract it from the total. Hope someone finds this usefull :)

1 remarks:

Post a Comment

Link to this post if you found it usefull

Calculating business days between dates in SQL (a datediff function without weekends)