How to find list of all Mondays in 2006

I have a one problem in my sql server. I would like to find out the list of all Monday's dates in the year 2006. Can anybody help me out?.declare @.indate datetime
set @.indate = getdate()

select
case cast(datediff(d,'1/1/1900',@.indate) % 7
as int)
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|||Cursor? Yeck.
set nocount on
declare @.TargetYear datetime
set @.TargetYear = '01-01-2006'

declare @.SequentialValues table (SequenceValue int)
declare @.SeedValue int
set @.SeedValue = 0

while @.SeedValue < 52
begin
insert into @.SequentialValues (SequenceValue) values(@.SeedValue)
set @.SeedValue = @.SeedValue + 1
end

select dateadd(day, -(datepart(dw, @.TargetYear) + 7) % 7 + 2, @.TargetYear) + (SequenceValue * 7)
from @.SequentialValues
where year(dateadd(day, -(datepart(dw, @.TargetYear) + 7) % 7 + 2, @.TargetYear) + (SequenceValue * 7)) = year(@.TargetYear)|||when modulo year / 7 = 3 there are 53 mondays in the year you will need to account for that
WRONG forgot leap year.

How to get the 53rd Monday. ??

ahh summer school|||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.|||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.A right boon. I just ran this (actually, Blindman very indirectly referred me to it):
http://sqljunkies.com/WebLog/amachanic/articles/NumbersTable.aspx

I would agree that it is useful and easy. I think he overdoes it when he describes it as fun :)|||pootie, i'm surprised you only just now clued to the numbers table

see this post (http://dbforums.com/showpost.php?p=4493522&postcount=7) ;)|||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-PatP|||set noCount on
Declare @.lcl_date datetime
,@.diff int
,@.current_date datetime
Select @.lcl_date = '2006/01/01'

Select @.diff = case (datepart(dw,@.lcl_date))
when 1 then 1
when 2 then 0
when 3 then -1
when 4 then -2
when 5 then -3
when 6 then -4
when 7 then -5
end

Select @.diff

-- 1st monday of the year
Select @.current_date = dateadd(dd,@.diff,@.lcl_date)
Select @.diff = 7

Select @.current_date

while (dateadd(dd,@.diff,@.current_date) <= '2006/12/31')
begin
Select dateadd(dd,@.diff,@.current_date)
Select @.current_date = dateadd(dd,@.diff,@.current_date)
end

set noCount off|||just comment out

Select @.diff
i got confused wen i tested the code for year%3 gives 3 for 53 mondays...
i got 54 recds cos of the select @.diff...

u can test for 2005/01/01 for 53 mondays|||Pat Phelan
ur code gives 52 mondays for 2005|||pootie, i'm surprised you only just now clued to the numbers table

see this post (http://dbforums.com/showpost.php?p=4493522&postcount=7) ;) 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 :)|||Pat Phelan
ur code gives 52 mondays for 2005
Pat's code is working excellent,and in my calender of 2005 I found 52 Mondays only ;)...Check out 1996 it contains 53 mondays
then 2001,2007, 2012 , 2024 .... all have 53 Mondays|||Pat Phelan
ur code gives 52 mondays for 2005How many were you hoping for?

-PatP|||my mistake Pat...

my code shud have been

set noCount on
Declare @.lcl_date datetime
,@.diff int
,@.current_date datetime
Select @.lcl_date = '2005/01/01'

Select @.diff = case (datepart(dw,@.lcl_date))
when 1 then 1
when 2 then 0
when 3 then 6
when 4 then 5
when 5 then 4
when 6 then 3
when 7 then 2
end

--Select @.diff

-- 1st monday of the year
Select @.current_date = dateadd(dd,@.diff,@.lcl_date)
Select @.diff = 7

Select @.current_date

while (dateadd(dd,@.diff,@.current_date) <= '2005/12/31')
begin
Select dateadd(dd,@.diff,@.current_date)
Select @.current_date = dateadd(dd,@.diff,@.current_date)
end

set noCount off|||Well, I think this cat has been skinned from every possible direction...|||Well, I think this cat has been skinned from every possible direction...Oh no! There are more ways to skin a cat than there are cats... But that is no reason to stop trying!

-PatP|||Talk about rocket science

How about this:

SELECT DATEADD(dd,n,'1/1/2006')
FROM n
WHERE DATEPART(dw,DATEADD(dd,n,'1/1/2006'))=2
AND n BETWEEN 1 AND 365|||How about this:You can still get bit with that solution. 1928 and 2012 don't work, among others.

-PatP|||Perhaps this method is the simplest:select DateValue as Monday
from WorkDays
where Productivity is null

or if you have a more lyrical sense:select DateValue as Monday
from Calendar
where dbo.GetMeDown(DateValue) = 1
and Weather = 'Rainy'|||OK, I'll byte

How come?|||How about this:

SELECT DATEADD(dd,n,'1/1/2006')
FROM n
WHERE DATEPART(dw,DATEADD(dd,n,'1/1/2006'))=2
AND n BETWEEN 1 AND 365

for one thing, you haven't made it clear where n (the table) and n (the column) come from

secondly, you're missing january the 1st, dude

rocket surgery indeed :)|||OK, I'll byte

How come?
http://www.vex.net/~paulmac/carpenter/lyrics/rainy_days_and_mondays.html|||Oh no! There are more ways to skin a cat than there are cats... But that is no reason to stop trying!

-PatP

There are many ways to skin a cat... but the cat isn't going to like any one of them!