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

Question

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 1

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)