views:

1160

answers:

5

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!

A: 

Try the DATENAME() function:

select [date_created]
from table
where DATENAME(WEEKDAY, [date_created]) <> 'Saturday'
  and DATENAME(WEEKDAY, [date_created]) <> 'Sunday'
Andrew
IS there a special reason why you answered your own question, or did the answer really occur to you after just 2 minutes?
Maximilian Mayerl
Answering own questions is legal in stackoverflow. From FAQ: "It's also perfectly fine to ask and answer your own question, but pretend you're on Jeopardy: phrase it in the form of a question."
ercan
Of course, this will only work in English-speaking countries.
gkrogers
@Maximilian Mayerl - I thought it would be helpful to others. And I also wondered if there was a better alternative to the one I found. I understand it is encouraged to ask a question then post an answer.. from the faq: "It's also perfectly fine to ask and answer your own question"
Andrew
+3  A: 
gkrogers
It's possible to automatically handle any possible settings of `DATEFIRST`. See my answer for details.
LukeH
Excellent tip: +1! :-)
gkrogers
+2  A: 

The answer depends on your server's week-start set up, so it's either

SELECT [date_created] FROM table WHERE DATEPART(w,[date_created]) NOT IN (7,1)

if Sunday is the first day of the week for your server

or

SELECT [date_created] FROM table WHERE DATEPART(w,[date_created]) NOT IN (6,7)

if Monday is the first day of the week for your server

Comment if you've got any questions :-)

Gausie
It's possible to automatically handle any possible settings of `DATEFIRST`. See my answer for details.
LukeH
Nice one Luke, I've been wondering how to do that...
Gausie
+9  A: 

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)
LukeH
Nice refinement: +1! :-)
gkrogers
A: 
SELECT date_created
FROM your_table
WHERE DATENAME(dw, date_created) NOT IN ('Saturday', 'Sunday')
kevchadders