CODEWITHSUNDEEP
datetimesql-server-2012case
Zerotoinfinity
Zerotoinfinity

Reputation: 6540

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

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.

Upvotes: 0

Views: 203

Answers (1)

lc.
lc.

Reputation: 116478

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)

Upvotes: 1

Related Questions