Amit,
Nice function - I think one small change could also speed it up - since weeks are a predictable format, when you encounter a Monday skip ahead the next 6 days - the second dateadd will then add one more day - once you find a Monday, you only need to check the Mondays....
Create Function Monday(@dt1 datetime,@dt2 datetime) returns int
as
Begin
Declare @cnt int,@dt as datetime
set @cnt=0
if @dt1 < @dt2
Begin
set @dt=@dt1
Begin
if Datepart(dw,@dt)=1
Begin
set @cnt=@cnt+1
set @dt=dateadd(dd,6,@dt)
End
set @dt=dateadd(dd,1,@dt)
End
End
if @cnt=0
Begin
return 0
End
Return @cnt
End
select dbo.Monday('2005-01-01','2005-02-28')
Regards,
Harley