71

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!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Andrew
  • 12,991
  • 15
  • 55
  • 85

7 Answers7

140

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
  • 263,068
  • 57
  • 365
  • 409
24
SELECT date_created
FROM your_table
WHERE DATENAME(dw, date_created) NOT IN ('Saturday', 'Sunday')
kevchadders
  • 8,335
  • 4
  • 42
  • 61
  • 2
    This might be problematic though. Run this script: `set language italian; select DATENAME(WEEKDAY, '2015-01-01');` The result is `giovedì`. – HuBeZa Jun 08 '15 at 07:23
  • 2
    Good 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 – kevchadders Jun 08 '15 at 09:04
16

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

gkrogers
  • 8,126
  • 3
  • 29
  • 36
  • It's possible to automatically handle any possible settings of `DATEFIRST`. See my answer for details. – LukeH Nov 26 '09 at 14:49
11

Try the DATENAME() function:

select [date_created]
from table
where DATENAME(WEEKDAY, [date_created]) <> 'Saturday'
  and DATENAME(WEEKDAY, [date_created]) <> 'Sunday'
Andrew
  • 12,991
  • 15
  • 55
  • 85
4

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
  • 4,291
  • 1
  • 25
  • 36
  • It's possible to automatically handle any possible settings of `DATEFIRST`. See my answer for details. – LukeH Nov 26 '09 at 14:50
3

Calculate Leave working days in a table column as a default value--updated

If you are using SQL here is the query which can help you: http://gallery.technet.microsoft.com/Calculate...

Joseph Daigle
  • 47,650
  • 10
  • 49
  • 73
Sarabpreet Singh Anand
  • 31
  • 1
0

Try this code

select (DATEDIFF(DD,'2014-08-01','2014-08-14')+1)- (DATEDIFF(WK,'2014-08-01','2014-08-14')* 2)
Tanmay Nehete
  • 2,138
  • 4
  • 31
  • 42