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

date, sql-server, t-sql, weekend

Solution

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)

Problem

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!

Original source