How do I exclude Weekend days in a SQL Server query?

sql-server tsql date weekend
Andrew picture Andrew · Nov 26, 2009 · Viewed 129.4k times · Source

How do I exclude values in a DateTime column that are Saturdays or Sundays?

For example, given the following data:

date_created
'2009-11-26 09:00:00'  -- Thursday
'2009-11-27 09:00:00'  -- Friday
'2009-11-28 09:00:00'  -- Saturday
'2009-11-29 09:00:00'  -- Sunday
'2009-11-30 09:00:00'  -- Monday

this is the result I'm looking for:

date_created
'2009-11-26 09:00:00'  -- Thursday
'2009-11-27 09:00:00'  -- Friday
'2009-11-30 09:00:00'  -- Monday

Thanks!

Answer

LukeH picture LukeH · Nov 26, 2009

When dealing with day-of-week calculations, it's important to take account of the current DATEFIRST settings. This query will always correctly exclude weekend days, using @@DATEFIRST to account for any possible setting for the first day of the week.

SELECT *
FROM your_table
WHERE ((DATEPART(dw, date_created) + @@DATEFIRST) % 7) NOT IN (0, 1)

Related questions

How to return only the Date from a SQL Server DateTime datatype

SELECT GETDATE() Returns: 2008-09-22 15:24:13.790 I want that date part without the time part: 2008-09-22 00:00:00.000 How can I get that?

Read More
Best approach to remove time part of datetime in SQL Server

Which method provides the best performance when removing the time portion from a datetime field in SQL Server? a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) or b) select cast(convert(char(11), getdate(), 113) as datetime) The second method does send a …

Read More
MS SQL compare dates?

I have 2 dates (datetimes): date1 = 2010-12-31 15:13:48.593 date2 = 2010-12-31 00:00:00.000 Its the same day, just different times. Comparing date1 and date2 using <= doesnt work because of the date1 time. So date1 <= date2 is wrong, but should be true. …

Read More