Skip to content
Advertisement

T-SQL get number of working days between 2 dates

I want to calculate the number of working days between 2 given dates. For example if I want to calculate the working days between 2013-01-10 and 2013-01-15, the result must be 3 working days (I don’t take into consideration the last day in that interval and I subtract the Saturdays and Sundays). I have the following code that works for most of the cases, except the one in my example.

How can I accomplish this? Do I have to go through all the days and check them? Or is there an easy way to do this.

Advertisement

Answer

Please, please, please use a calendar table. SQL Server doesn’t know anything about national holidays, company events, natural disasters, etc. A calendar table is fairly easy to build, takes an extremely small amount of space, and will be in memory if it is referenced enough.

Here is an example that creates a calendar table with 30 years of dates (2000 -> 2029) but requires only 200 KB on disk (136 KB if you use page compression). That is almost guaranteed to be less than the memory grant required to process some CTE or other set at runtime.

Now the query you’re after is quite simple to write:

More info on calendar tables:

http://web.archive.org/web/20070611150639/http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

More info on generating sets without loops:

http://www.sqlperformance.com/tag/date-ranges

Also beware of little things like relying on the English output of DATENAME. I’ve seen several applications break because some users had a different language setting, and if you’re relying on WEEKDAY be sure you set your DATEFIRST setting appropriately…

Advertisement