Select weekend or weekday data from a table based on date param

Question

How can I select data from a table based on weekday or weekend, like if date is a weekday then select only historical weekday data from the table & if date is a weekend then select only historical weekend data.

I have tried to do that in this way but no luck

DECLARE @MyDate DATE = '08/17/2013'

SELECT datename(dw,@MyDate)

SELECT * FROM MyTable
WHERE 
datename(dw,DateColumnInTable) IN ( 
CASE WHEN (datename(dw,@MyDate) IN ('Saturday','Sunday')) THEN '''Saturday'',''Sunday'''
ELSE  'Monday'',''Tuesday'',''Wednesday'',''Thursday'',''Friday'
END )

Any I can see lots of data in my table for saturday and sunday but this query is giving me blank record set.

Why down vote? I tried first then I did google about it, after that I search SO and my question is not subjective. Where I am wrong ? — Zerotoinfinity, Aug 20 '13 at 15:51

lc. · Accepted Answer · 2013-08-21T00:58:50.757

1

Here's one way:

DECLARE @MyDate DATE = '08/17/2013'

IF (DATEPART(weekday, @MyDate) IN (1,7))
    SELECT * 
    FROM MyTable
    WHERE DATEPART(weekday, DateColumnInTable) IN (1,7)
ELSE
    SELECT * 
    FROM MyTable
    WHERE DATEPART(weekday, DateColumnInTable) BETWEEN 2 AND 6

If you would like to do it in one clause you can do something like the following, but it may perform worse:

SELECT *
FROM MyTable
WHERE (DATEPART(weekday, @MyDate) IN (1,7) AND DATEPART(weekday, DateColumnInTable) IN (1,7))
OR (DATEPART(weekday, @MyDate) BETWEEN 2 AND 6 AND DATEPART(weekday, DateColumnInTable) BETWEEN 2 AND 6)

edited Aug 21 '13 at 00:58

answered Aug 17 '13 at 15:05

lc.

113,939
20
158
187

DATEPART(weekday,...) isn't deterministic – Bogdan Sahlean Aug 17 '13 at 16:00
@BogdanSahlean No it's not, but you can either use a dates table and join on that, or refer to http://stackoverflow.com/questions/1803561/deterministic-scalar-function-to-get-day-of-week-for-a-date – lc. Aug 17 '13 at 16:01
@Ic My Select clause is quite big so can I do it in one clause ? – Zerotoinfinity Aug 20 '13 at 15:54
@Zerotoinfinite Edited to include one option – lc. Aug 21 '13 at 00:59
@Ic. Thanks a lot :) but I still don't know why I got a down vote. Nevertheless, thanks for pushing it though – Zerotoinfinity Aug 21 '13 at 20:43
I'm not sure about the downvote (it wasn't me) but glad this helped – lc. Aug 22 '13 at 03:57

Select weekend or weekday data from a table based on date param

1 Answers1