SDU Tools: DateDiffNoWeekends

There is a SQL Server T-SQL function that calculates the number of days (or months, or years, or other time periods) between any two dates.

SELECT DATEDIFF(day, 'firstdate', 'seconddate');

But I regularly get asked how to work out the number of days between two dates, excluding the weekends (Saturday and Sunday).

I've seen a few functions around that do a pretty good job at this but many don't work properly if you have DATEFIRST changed in your session ie: if you change the first day of the week.

So I added a tool to SDU Tools to calculate this, and ensuring that the DATEFIRST setting doesn't cause it issues. You can use it like this:

SELECT SDU_Tools.DateDiffNoWeekends('firstdate', 'seconddate');

You can see it in action here:

You can find out more about our free SDU Tools here:

http://sdutools.sqldownunder.com

 

Leave a Reply

Your email address will not be published.