Like any other enterprise RDBMS system, SQL Server ships with several built-in functions that make developers’ T-SQL code clean, convenient and reusable. To demonstrate the efficiency of functions, say we needed to retrieve a server name for one of our SQL Server instances. Well, one of doing this would be to write a SELECT statement that would query the system view [sys].[servers] from the master database as shown in Script 1.
|
1 2 3 4 5 6 7 8 |
USE master; GO SELECT [name] AS [Server Name] FROM [sys].[servers]; GO |
However, another simpler and cleaner approach is for me to simply call on the @@SERVERNAME built-in function shown in Script 2
|
1 2 3 4 |
SELECT @@SERVERNAME AS [Server Name]; GO |
Unfortunately, it is impossible for SQL Server to provide built-in functions for everything that developers would need. That is why – as developers – we are given an ability to create our own user-defined functions. One such common user-defined function involves the ability to calculate the total number of working days and working hours within a given date range. I have personally noticed a need for such a function in cases whereby some KPIs relates to determining a total number of days/time it takes to resolve a customer complaint. In this article, we take a look at some of the tricks and T-SQL methods that can be used to easily create a user-defined function that calculates working days and hours.
Calculate Working Days using DATEDIFF
In this approach, we employ several steps that make use of DATEDIFF and DATEPART functions to successfully determine working days.
Step 1: Calculate the total number of days between a date range
In this step, we use the DAY interval within the DATEDIFF function to determine the number of days between two dates. The output of this calculation is stored in the @TotDays variable, as shown in Script 3.
|
1 2 3 4 5 6 7 8 |
DECLARE @DateFrom DATETIME; DECLARE @DateTo DATETIME; SET @DateFrom = '2017-06-03 11:19:11.287'; SET @DateTo = '2017-06-11 13:53:14.750'; DECLARE @TotDays INT= DATEDIFF(DAY, @DateFrom, @DateTo) |
Following the execution of Script 3, the value of the @TotDays variable is 8 days as shown in Figure 1.
The total of 8 days is actually incorrect as it is excluding the start date. Say for instance that for some reason you ended up working on the 25th of December 2016 – a Christmas day. If you were to perform a working day calculation in SQL Server using the DATEDIFF function as shown in Script 4, you would get an incorrect result of 0 total days as shown in Figure 2.
|
1 2 3 4 5 |
SET @DateFrom = '2016-12-25'; SET @DateTo = '2016-12-25'; DECLARE @TotDays INT= DATEDIFF(DAY, @DateFrom, @DateTo) AS [TotalDays]; |
One way to get around this issue is to always increment the output of a DATEDIFF function by 1, as shown in Script 5.
|
1 2 3 |
DECLARE @TotDays INT= DATEDIFF(DAY, @DateFrom, @DateTo) + 1 AS [TotalDays]; |
Following the increment by 1, the total number of days shown in Figure 1 changes from 8 to 9 as shown in Figure 3.
Step 2: Calculate the total number of weeks between a date range
Once we have obtained the total number of days, we now need to:
- Calculate the total number of weeks between two dates, and then
- Subtracts those number of weeks from the total number of days
In order to do this calculation, we again use the DATEDIFF function but this time we change the interval to week (represented as WEEK or WK). The output of the week calculation is stored in the @TotWeeks variable, as shown in Script 6.
|
1 2 3 |
DECLARE @TotWeeks INT= DATEDIFF(WEEK, @DateFrom, @DateTo); |
Given the date range specified in Script 3, our week calculation returns 2 as shown in Figure 4.
Again, just as in the calculation of days, the output of the week calculation – 2 weeks – is incorrect. This time the issue is as a result of the way that WEEK interval works within the DATEDIFF function. The WEEK interval in DATEDIFF does not actually calculate the number of weeks, instead it calculates the number of instances that a complete weekend appears (combination of Saturday and Sunday) within the specified date range. Consequently, for a more accurate week calculation, we should always multiply the output by 2 – the number of days in a weekend. The revised script for calculating the number of weeks is shown below in Script 7.
|
1 2 3 |
DECLARE @TotWeeks INT= (DATEDIFF(WEEK, @DateFrom, @DateTo) * 2) AS [TotalWeeks]; |
The output of Script 7 basically doubles what was returned in Figure 4 from 2 to 4 weeks as shown in Figure 5.
Step 3: Exclude Incomplete Weekends
The final steps involve the exclusion of incomplete weekend days from being counted as part of working days. Incomplete weekend days refer to instances whereby the Date From parameter value falls on a Sunday or the Date To parameter value is on a Saturday. The exclusion of incomplete weekends can be done by either using DATENAME or DATEPART functions. Whenever you can, refrain from using the DATEPART in calculating working days as it is affected by your language settings of your SQL Server instance. For instance, Script 8 returns Sunday as day name for both US and British language settings.
|
1 2 3 4 5 6 7 |
SET LANGUAGE us_english; SELECT DATENAME(weekday, '20170611') [US]; SET LANGUAGE British; SELECT DATENAME(weekday, '20170611') [British]; |
However, when DATEPART function is used as shown in Script 9, we get different values for US and British settings.
|
1 2 3 4 5 6 7 |
SET LANGUAGE us_english; SELECT DATEPART(weekday, '20170611') [US]; SET LANGUAGE British; SELECT DATEPART(weekday, '20170611') [British]; |
Script 10 shows the complete definition for a user defined function that calculates working days by mostly using the DATEDIFF function.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
CREATE FUNCTION [dbo].[fn_GetTotalWorkingDays] ( @DateFrom Date, @DateTo Date ) RETURNS INT AS BEGIN DECLARE @TotDays INT= DATEDIFF(DAY, @DateFrom, @DateTo) + 1; DECLARE @TotWeeks INT= DATEDIFF(WEEK, @DateFrom, @DateTo) * 2; DECLARE @IsSunday INT= CASE WHEN DATENAME(WEEKDAY, @DateFrom) = 'Sunday' THEN 1 ELSE 0 END; DECLARE @IsSaturday INT= CASE WHEN DATENAME(WEEKDAY, @DateTo) = 'Saturday' THEN 1 ELSE 0 END; DECLARE @TotWorkingDays INT= @TotDays - @TotWeeks - @IsSunday + @IsSaturday; RETURN @TotWorkingDays; END |
Now if we call this function as shown in Script 11, 5 is returned in Figure 8 – which is actually the correct number of working days between the 3rd and 11th of June 2017.
|
1 2 3 |
SELECT [dbo].[fn_GetTotalWorkingDays] ('2017-06-03','2017-06-11') |
Calculate Working Days using WHILE Loop
Another approach to calculating working days is to use a WHILE loop which basically iterates through a date range and increment it by 1 whenever days are found to be within Monday – Friday. The complete script for calculating working days using the WHILE loop is shown in Script 12.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
CREATE FUNCTION [dbo].[fn_GetTotalWorkingDaysUsingLoop] (@DateFrom DATE, @DateTo DATE ) RETURNS INT AS BEGIN DECLARE @TotWorkingDays INT= 0; WHILE @DateFrom <= @DateTo BEGIN IF DATENAME(WEEKDAY, @DateFrom) IN('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday') BEGIN SET @TotWorkingDays = @TotWorkingDays + 1; END; SET @DateFrom = DATEADD(DAY, 1, @DateFrom); END; RETURN @TotWorkingDays; END; GO |
Although the WHILE loop option is cleaner and uses less lines of code, it has the potential of being a performance bottleneck in your environment particularly when your date range spans across several years.
Calculate Working Hours
The final section of this article involves the calculation of working hours based on a given date range.
Step 1: Calculate total working days
In this step, we use a similar approach to the previous sections whereby we calculate the total working days. The only difference is that we are not incrementing the output by 1 as shown in Script 13.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
DECLARE @TotalWorkDays INT, @TotalTimeDiff DECIMAL(18, 2), @DateFrom DATETIME, @DateTo DATETIME; SET @DateFrom = '2017-06-05 11:19:11.287'; SET @DateTo = '2017-06-07 09:53:14.750'; SET @TotalWorkDays = DATEDIFF(DAY, @DateFrom, @DateTo) -(DATEDIFF(WEEK, @DateFrom, @DateTo) * 2) -CASE WHEN DATENAME(WEEKDAY, @DateFrom) = 'Sunday' THEN 1 ELSE 0 END+CASE WHEN DATENAME(WEEKDAY, @DateTo) = 'Saturday' THEN 1 ELSE 0 END; |
Step 2: Calculate total number of seconds
The next part involves getting a difference in seconds between the two dates and converting that difference into hours by dividing by 3600.0 as shown in Script 14.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SET @TotalTimeDiff = ( SELECT DATEDIFF(SECOND, ( SELECT CONVERT(TIME, @DateFrom) ), ( SELECT CONVERT(TIME, @DateTo) )) / 3600.0 ); |
The last part involves multiplying the output of Step 1 by 24 (total number of hours in a day) and then later adding that to the output of Step 2 as shown in Script 15.
|
1 2 3 |
SELECT(@TotalWorkDays * 24.00) + @TotalTimeDiff; |
Finally, the complete script that can be used to create a user defined function for calculating working hours is shown in Script 16 and its application is shown in Figure 9.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
CREATE FUNCTION [dbo].[fn_GetTotalWorkingHours] ( @DateFrom Datetime, @DateTo Datetime ) RETURNS DECIMAL(18,2) AS BEGIN DECLARE @TotalWorkDays INT, @TotalTimeDiff DECIMAL(18, 2) SET @TotalWorkDays = DATEDIFF(DAY, @DateFrom, @DateTo) -(DATEDIFF(WEEK, @DateFrom, @DateTo) * 2) -CASE WHEN DATENAME(WEEKDAY, @DateFrom) = 'Sunday' THEN 1 ELSE 0 END+CASE WHEN DATENAME(WEEKDAY, @DateTo) = 'Saturday' THEN 1 ELSE 0 END; SET @TotalTimeDiff = ( SELECT DATEDIFF(SECOND, ( SELECT CONVERT(TIME, @DateFrom) ), ( SELECT CONVERT(TIME, @DateTo) )) / 3600.0 ); RETURN (SELECT(@TotalWorkDays * 24.00) + @TotalTimeDiff) END GO |
Sifiso's LinkedIn profile
View all posts by Sifiso W. Ndlovu