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"
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:
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.
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 ?
Well... it doesn't. It generate some numbers between 0 and 32, but not all of them. The above query only generates 27 values. If you want to generate each number between 0 and 31, you can use this query:
I modified your function as follows:
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:
Of course, it's very unlikely that anyone would need such a long period (and for shorter periods the difference in performance is very small).