This tutorial demonstrates 2 ways for you how to obtain the number of working days and business hours in SQL Server based on between date start and end date parameters.This function is more popular in doing business related to human resource, particularly in calculating salary of employee :).
Below is a case for demonstration :
The input :
+ Start date : ‘2012-03-01’
+ End date : ‘2012-03-31’
The output :
Working Days and Business Hours Result
1. The first way : Get total number of days based on DATEDIFF function then subtract weekend days
--Calculate and return the number of workdays based on the input parameters. CREATE FUNCTION [dbo].[fGetWorkingDays] ( @StartDate DATETIME, -- start date @EndDate DATETIME -- end date ) RETURNS INT AS BEGIN --If the Start Date or End Date is null, return NULL. IF (@StartDate IS NULL OR @EndDate IS NULL) RETURN NULL --If @StartDate is greater than @EndDate, return -1 IF @StartDate > @EndDate RETURN -1 RETURN ( SELECT (DATEDIFF(dd,@StartDate, @EndDate) + 1) -- Get total number of days including weekends - (DATEDIFF(wk,@StartDate, @EndDate) * 2) -- Subtact 2 days for each full weekend - (CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' -- If Start Date is Sunday, subtract 1 THEN 1 ELSE 0 END) - (CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' -- If End Date is Saturday, subtract 1 THEN 1 ELSE 0 END) ) END |
2. The second way : Get total number of days based on UDF function fGetListOfDates – Get list of dates between start date and end date without including weekend days
--Calculate and return the number of workdays based on the input parameters. CREATE FUNCTION [dbo].[fGetWorkingDays] ( @StartDate DATETIME, -- start date @EndDate DATETIME -- end date ) RETURNS INT AS BEGIN --If the Start Date or End Date is null, return NULL. IF (@StartDate IS NULL OR @EndDate IS NULL) RETURN NULL --If @StartDate is greater than @EndDate, return -1 IF @StartDate > @EndDate RETURN -1 RETURN ( SELECT COUNT(LD.NameOfDay) FROM dbo.fGetListOfDates(@StartDate, @EndDate) LD WHERE LD.NameOfDay NOT IN ('Sunday','Saturday') ) END |
3. After create this function,then run this below statements, we will get expected result as the above image
DECLARE @StartDate DATETIME, @EndDate DATETIME SET @StartDate = dbo.GetFirstDayOfMonth(GETDATE(), 0) -- @StartDate = '03-01-2012' SET @EndDate = dbo.GetLastDayOfMonth(GETDATE(), 0) -- @EndDate = '03-31-2012' SELECT dbo.fGetWorkingDays(@StartDate,@EndDate) AS NumberOfWorkingDays SELECT dbo.fGetWorkingDays(@StartDate,@EndDate) * 8 AS BusinessHours |
With above test script , it mentions to two functions GetFirstDayOfMonth and GetLastDayOfMonth, in order to understand these functions you can refer to SQL Server get first/last day of previous/current/next month
Hope it helpful for you :)!
