How to count Mondays between two dates

[2ni]

I want to know how many monday i have between 2 dates.
Anyone can help me please ?

 

[bertrandkis]

Pretty easy folks! Here is how I do it.

declare @d1 datetime,@d2 datetime,@dx datetime,@x int,@mondays int
--the first date is taken from the sales table in the pubs
--database but u can use your own date.

--initialize first date
set @d1=(select max(ord_date) from sales)

--let's say the second date is 25 days away from first date
set @d2=dateadd(day,25,@d1)

--inititalize intermediate date variable
set @dx=@d1

-- initialize loop counter
set @x=0

set @mondays=0 --number of mondays
while (@dx<>@d2)
begin
set @dx=(select dateadd(day,@x,@d1))

if( (select datename(dw,@dx))='Monday')
set @mondays=@mondays+1

set @x=@x+1 --increment loop counter
end
select cast(@mondays as varchar(2))+ ' Mondays'

I hope this Helps.
Bertrandkis 'Black belt SQL Programmer'

 

[pascalsql]

/* I spent 3 hours to find out but worth it!
note: I initiated the thread
*/


declare @d1 datetime -- start date
declare @d2 datetime -- end date
declare @nb_days smallint
declare @wd smallint -- weekday to count
declare @j1 smallint -- weekday for start date
declare @j2 smallint -- weekday for endte

select @d1 = '19-03-2003'
select @d2 = '26-03-2003'
select @wd = 3 -- wednesday, depends on @@datefirst

select @nb_days = datediff(d, @d1, @d2) + 1 -- includes start and end
select @j1 = datepart(dw, @d1)
select @j2 = datepart(dw, @d2)

select @nb_days/7 + case when (@j1+@nb_days%7) > @wd + (case when @wd<@j1 then 7 else 0 end)
then 1
else 0
end

/* a loop would not be very efficient in a select.
cursors = :-(
*/

-- Have a good day!

 

[SQLSister]

Can I steal that for my FAQ I'm working on concerning how to avoid cursors? Many people would have done this with a cursor.