Excluding weekends from a SQL date range

Recently I wanted to find the time between 2 datetimes, but I wanted to exclude weekends.  To further complicate matters, I wanted to know hours, not just days.

The “standard” way to do stuff like this is to build out a calendar table that has every date for the next 50 years, along with info like “IsWeekend” or “IsHoliday”.

But I found some snippets around that I hoped to use to my advantage, and what I came up with works very well:

DECLARE @start DATETIME
DECLARE @end DATETIME

SET @start = '3/7/08 12:50 pm'
SET @end = '3/11/08 1:50 am'

SELECT (DateDiff(hh, @start, @end) - DateDiff(ww, @start, @end)*2*24)/24.0

I won’t spend a lot of time explaining why it works, but it basically counts the number of weekend “jumps” that are crossed between the 2 dates, and subtracts accordingly.

 

Advertisements

3 thoughts on “Excluding weekends from a SQL date range

  1. Pingback: T-SQL: Calculating number of weekdays between two dates

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s