Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/plarsson/howmanymoremondaysuntiliretire.asp

Nice article,  alternatively...

SET DATEFIRST 1 -- force monday as the beginning of the week

DECLARE @bdy datetime,
 @RetiralAge smallint

SET @bdy='24-jul-1971'
SET @RetiralAge=65

SELECT  datediff (dd,getdate()+(8-datepart(dw,getdate())),dateadd(year,@RetiralAge,@bdy))/7  as "Mondays till you retire"

-Ally

True, and it works too. But maybe not that versatile?

Calculating the number of Mondays between two dates was the subject of the following thread:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=258968

My suggestion was the following:

select datediff(d, '19000101', @dateTo)/7 - datediff(d, '19000102', @dateFrom)/7

I still believe this is the fastest method to calculate the number of Mondays between two dates. On the other hand, it is not very flexible...

Yes, it works well if not more than one of the two dates already is a monday. Using this code

select (datediff(d, '20060213', '20060724') + 1)/7 - datediff(d, '20060213', '20060717')/7

reports 1 monday to me, where it should report 2 mondays.

But your query is very fast.

No, no, use the formula above:

select datediff(d, '19000101', '20060724')/7 - datediff(d, '19000102', '20060717')/7

returns 2, i.e 2 Mondays between the 17th and the 24th.

Ok, ok ok

Still don't get proper result with date range 1899-12-29 and 1900-01-03.

Running

select datediff(d, '19000101', '1900-01-03')/7 - datediff(d, '19000102', '1899-12-29')/7

gives me 0 mondays. Shouldn't it report 1 monday for January 1, 1900?

Do you really need dates that old?

You could use

select datediff(d, '18000106', @dateTo)/7 - datediff(d, '18000107', @dateFrom)/7

instead. Note that

select datediff(d, '18000106', '1900-01-03')/7 - datediff(d, '18000107', '1899-12-29')/7

gives you one Monday.

My formula needs two reference dates, a Monday (e.g. 1900-01-01 or 1800-01-06) and the following Tuesday (e.g. 1900-01-02 or 1800-01-07). Both @dateFrom and @dateTo must be larger than these days for the formula to work. But OK, you found the weak point

In the first code sample, I suppose that this portion should return each number between 0 and 32, right ?

I modified your function as follows:

CREATE FUNCTION dbo.MySeqDates (@LowDate DATETIME, @HighDate DATETIME)
RETURNS @Dates TABLE (SeqDate DATETIME)
AS
BEGIN
DECLARE @Temp DATETIME

IF @LowDate > @HighDate
 SELECT @Temp = @LowDate,
  @LowDate = DATEADD(day, DATEDIFF(day, 0, @HighDate), 0),
  @HighDate = DATEADD(day, DATEDIFF(day, 0, @Temp), 0)
 ELSE
 SELECT @LowDate = DATEADD(day, DATEDIFF(day, 0, @LowDate), 0),
  @HighDate = DATEADD(day, DATEDIFF(day, 0, @HighDate), 0)

INSERT @Dates (SeqDate) VALUES (@LowDate)

DECLARE @TotalRows int, @RowCnt int
SET @TotalRows=1
SET @RowCnt=1

WHILE @RowCnt > 0 BEGIN
 INSERT @Dates (SeqDate)
 SELECT DATEADD(dd, @TotalRows, d.SeqDate)
 FROM @Dates d
 WHERE DATEADD(dd, @TotalRows, d.SeqDate) <= @HighDate

 SET @RowCnt=@@ROWCOUNT
 SET @TotalRows=@TotalRows+@RowCnt
END

RETURN
END

The difference is that I use a variable to count the number of rows (instead of using COUNT() in a derived table) which should be faster if the number of rows is really big. To benchmark, try running something like this: