Click on the banner to learn about and purchase my database training on Azure

How to calculate business days in SQL Server (dCalendar table)

Views: 27.929 views
Reading Time: 5 minutes

Hello guys,
Good day.

In this post I will show how to perform various calculations with weekdays in SQL Server, creating a table with all the information already calculated and remaining just perform some simple SELECTs to get this information.

Prerequisite: Holiday Table

A prerequisite for this post is that you have already created the holiday table I commented on in the post. How to create a table of holidays (national, state, and mobile) in SQL Server. This table will be used to identify whether a specific date is a holiday or not.

Prerequisite: Functions for Business Day Calculation

Another prerequisite for our table, are 3 functions used to return business days, as below:




Creating the weekday table

After creating all the necessary prerequisites, let's create our weekday table.

With this, we get the following table:
SQL Server - Business Days and Holidays

Creating the business day functions

Once we create our Dia_Util table, we can use the functions below to make it easier to get the information:




Testing the functions:
SQL Server - How to calculate weekdays and holidays table function

That's it folks!
Until the next post!