Introduction
This is a real quick post to explain how you can calculate the number of work days between 2 dates in SQL Server. This post considers the Workdays from Monday to Friday. In case you want the workdays from Monday to Saturday, read on. So, let us write a function that will display the number of work days between 2 dates in SQL Server.
CREATE 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) + 1)
-(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
In case you want the workdays to be calculated from Monday to Saturday, use the function below instead:
CREATE 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) + 1)
-(DATEDIFF(wk, @StartDate, @EndDate))
-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
RETURN @WORKDAYS
END
Hope you like this post. In case you know any other method of calculating the work days, let us know via comments. Cheers!
A passionate developer with over 10 years of experience and building my software company code by code. Experience withMS Technologies like .Net | MVC | Xamarin | Sharepoint | MS Project Server and PhP along with open source CMS Systems like Wordpress/DotNetNuke etc.
Love to debug problems and solve them. I love writing articles on my website in my spare time. Please visit my Website for more details and subscribe to get technology related tips/tricks. #SOreadytohelp