How do I exclude Weekend days in a SQL Server query?
Solution 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)
Solution 2
SELECT date_created
FROM your_table
WHERE DATENAME(dw, date_created) NOT IN ('Saturday', 'Sunday')
Solution 3
Assuming you're using SQL Server, use DATEPART with dw:
SELECT date_created
FROM your_table
WHERE DATEPART(dw, date_created) NOT IN (1, 7);
EDIT: I should point out that the actual numeric value returned by DATEPART(dw) is determined by the value set by using SET DATEFIRST:
http://msdn.microsoft.com/en-us/library/ms181598.aspx
Solution 4
Try the DATENAME()
function:
select [date_created]
from table
where DATENAME(WEEKDAY, [date_created]) <> 'Saturday'
and DATENAME(WEEKDAY, [date_created]) <> 'Sunday'
Solution 5
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 :-)
Andrew
I love designing, creating and supporting software that help computers get out of the way of busy people getting stuff done. Primarily for professional development and to improve my writing, I post occasionally on my blog
Updated on July 06, 2022Comments
-
Andrew almost 2 years
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!
-
LukeH over 14 yearsIt's possible to automatically handle any possible settings of
DATEFIRST
. See my answer for details. -
LukeH over 14 yearsIt's possible to automatically handle any possible settings of
DATEFIRST
. See my answer for details. -
HuBeZa about 9 yearsThis might be problematic though. Run this script:
set language italian; select DATENAME(WEEKDAY, '2015-01-01');
The result isgiovedì
. -
glagarto about 9 yearsGood point HuBeZa, for translating the day into another language I would run the following
SELECT * FROM sys.syslanguages
and look for the language you wish to translate to