Sifiso W. Ndlovu

How to calculate work days and hours in SQL Server

June 26, 2017 by

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.

Script 1

However, another simpler and cleaner approach is for me to simply call on the @@SERVERNAME built-in function shown in Script 2

Script 2

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 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.

Script 3

Following the execution of Script 3, the value of the @TotDays variable is 8 days as shown in Figure 1.


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.

Script 4


Figure 2

One way to get around this issue is to always increment the output of a DATEDIFF function by 1, as shown in Script 5.

Script 5

Following the increment by 1, the total number of days shown in Figure 1 changes from 8 to 9 as shown in Figure 3.


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.

Script 6

Given the date range specified in Script 3, our week calculation returns 2 as shown in Figure 4.


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 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 number of weeks is shown below in Script 7.

Script 7

The output of Script 7 basically doubles what was returned in Figure 4 from 2 to 4 weeks as shown in Figure 5.


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.

Script 8


Figure 6

However, when DATEPART function is used as shown in Script 9, we get different values for US and British settings.

Script 9


Figure 7

Script 10 shows the complete definition for a user defined function that calculates working days by mostly using the DATEDIFF function.

Script 10

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.

Script 11


Figure 8

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.

Script 12

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.

Script 13

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.

Script 14

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.

Script 15

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.

Script 16


Figure 9

Resources


Sifiso W. Ndlovu

Sifiso W. Ndlovu

Sifiso is a Johannesburg based certified professional within a wide range of Microsoft Technology Competencies such SQL Server and Visual Studio Application Lifecycle Management.

He is the member of the Johannesburg SQL User Group and also hold a Master’s Degree in MCom IT Management from the University of Johannesburg.

He currently works for Clientele Life as an Assistant Manager in Business Software Solutions.

View all posts by Sifiso W. Ndlovu
Sifiso W. Ndlovu
SQL Database development

About Sifiso W. Ndlovu

Sifiso is a Johannesburg based certified professional within a wide range of Microsoft Technology Competencies such SQL Server and Visual Studio Application Lifecycle Management. He is the member of the Johannesburg SQL User Group and also hold a Master’s Degree in MCom IT Management from the University of Johannesburg. He currently works for Clientele Life as an Assistant Manager in Business Software Solutions. View all posts by Sifiso W. Ndlovu

681 Views