Skip to content

Instantly share code, notes, and snippets.

@aromig
Created December 6, 2017 20:42
Show Gist options
  • Save aromig/261a0fa98f870466fa3a7ca67b2ea381 to your computer and use it in GitHub Desktop.
Save aromig/261a0fa98f870466fa3a7ca67b2ea381 to your computer and use it in GitHub Desktop.
/* Calculates the difference between dates excluding weekends */
ALTER FUNCTION [dbo].[CalculateNumberOfWorkDays] (@StartDate datetime, @EndDate datetime)
RETURNS int
AS
BEGIN
SET @StartDate = DATEADD(dd, DATEDIFF(dd, 0, @StartDate), 0)
SET @EndDate = DATEADD(dd, DATEDIFF(dd, 0, @EndDate), 0)
DECLARE @WORKDAYS INT
SELECT @WORKDAYS = (DATEDIFF(dd, @StartDate, @EndDate))
-(DATEDIFF(wk, @StartDate, @EndDate) * 2)
-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
RETURN @WORKDAYS
END
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment