How to find list of all Mondays in 2006

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to find list of all Mondays in 2006

Author

Topic

veparala
Starting Member

30 Posts

Posted - 2006-07-03 : 11:51:22

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?.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-03 : 12:03:42

select *
from
(select d = dateadd(dd,i,'20060101') from
(select i = i1.i + i2.i + i3.i + i4.i + i5.i + i6.i + i7.i + i8.i + i9.i
from
(select i = 0 union select 1) as i1 ,
(select i = 0 union select 2) as i2 ,
(select i = 0 union select 4) as i3 ,
(select i = 0 union select 8) as i4 ,
(select i = 0 union select 16) as i5 ,
(select i = 0 union select 32) as i6 ,
(select i = 0 union select 64) as i7 ,
(select i = 0 union select 128) as i8 ,
(select i = 0 union select 256) as i9
) as ints
where i between 0 and 370
) a
where d < '20070101'
and datename(dw,d) = 'Monday'
order by d

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-03 : 12:14:12

Or...

select date from dbo.F_TABLE_DATE('20060101', '20061231') where DAY_OF_WEEK = 2

The (very useful) F_TABLE_DATE function is here...
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

veparala
Starting Member

30 Posts

Posted - 2006-07-03 : 12:33:20

Thanks for helping....

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2006-07-04 : 03:20:02

I think the fact that Mondays are always every 7 days, you can make that query a little easier...

I'm going assume you already have a 'nums' table (mine starts at zero), rather than building one from scratch.

We know that the first Monday of the year is:
select dateadd(day,n.num,'20060101')
from nums n
where n.num < 7
and datename(dw,dateadd(day,n.num,'20060101')) = 'Monday'

So then we should be able to use this date to list the next 52:
select dateadd(week,w.num,(select dateadd(day,n.num,'20060101') from nums n where n.num < 7 and datename(dw,dateadd(day,n.num,'20060101')) = 'Monday'))
from nums w
where w.num < 52

Or if you want to be able to filter this to a specific date, try looking at the number of days, divided by 7:
select dateadd(week,w.num,(select dateadd(day,n.num,'20060101') from nums n where n.num < 7 and datename(dw,dateadd(day,n.num,'20060101')) = 'Monday'))
from nums w
where w.num <= datediff(day,(select dateadd(day,n.num,'20060101') from nums n where n.num < 7 and datename(dw,dateadd(day,n.num,'20060101')) = 'Monday'),'20061231')/7

Now, because the subqueries are independent, they only need to be calculated once each, so this is pretty fast.

Rob Farley
http://robfarley.blogspot.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources