case cast(datediff(d,'1/1/1900',@indate) % 7
when 0 then 'Monday'
when 1 then 'Tuesday'
when 2 then 'Wednesday'
when 3 then 'Thursday'
when 4 then 'Friday'
when 5 then 'Saturday'
when 6 then 'Sunday'
end as DOW
this will work you just need to cursor through the year
or you could use a calendar table and use this in the where clause
Bump the sequential values up to at least 32. Ideally, you should have a table of sequential values in your database anyway, populated up to a thousand or so. It greatly simplifies many queries involving date ranges.
If it's not practically useful, then it's practically useless.
You didn't mention which version of SQL Server you are using, so I'll give a generic answer that works for at least SQL 6.5 through SQL 2005. There are cleaner versions that are version specific, but this is portable.
DECLARE @iYear INT
SET @iYear = 2006
SELECT DateAdd(week, o1.v + o0.v, DateAdd(day, 2 - DatePart(dw
, Convert(VARCHAR(4), @iYear) + '-01-01'), Convert(VARCHAR(4)
, @iYear) + '-01-01'))
FROM (SELECT 0 AS v UNION SELECT 8 UNION SELECT 16 UNION SELECT 24
UNION SELECT 32 UNION SELECT 40 UNION SELECT 48) AS o1
CROSS JOIN (SELECT 0 AS v UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7) AS o0
WHERE @iYear = DatePart(year, DateAdd(week, o1.v + o0.v, DateAdd(day
, 2 - DatePart(dw, Convert(VARCHAR(4), @iYear) + '-01-01')
, Convert(VARCHAR(4), @iYear) + '-01-01')))
ORDER BY 1
Lol. Apols Rudy - there was a degree of ambiguity to my post. I meant I "merely" ran the code (i.e. having a numbers table is merely one F5 press away) rather than I had just recently run it. I don't know how I ever lived without my numbers table. It must have taken you sometime finding that old post though