SwePeso (7/24/2013)
I have now had some time to check the math for the formula.The short story is that if the anchor date is a leapyear the algorithm fails. If you change the anchor date to 20000101 or 18960101, the algorithm will give wrong result. That gives us non-leapyears to work with.
Any non-leapyear starting not starting with monday will faill too.
First day of year is Monday
+-----+-----+
| Yes | No |
|-----+-----+
Yes | F | F |
+-----+-----+
No | W | F |
+-----+-----+
Leap year
Accidentally, the date 00010101 will work as it is a non-leapyear starting with monday, and so will 17530101 and 19000101.
That doesn't appear to be entirely correct but anything before '19000101' certainly appears to be screwed. Here's the proof code that shows that. I sure wish I had thought of this test when I wrote the article. :blush: I'll have Steve pull the article and see if I can come up with a simple fix. Thanks, Peter.
--===== Build all dates from 1753-01-01 through 9999-12-31
WITH cteGenDates AS
(
SELECT TOP (DATEDIFF(dd,'1753','9999-12-31')+1)
Date = DATEADD(dd,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,'1753')
FROM master.sys.all_columns ac1,
master.sys.all_columns ac2
) --=== Do the calculated ISOWk from the article and the built in function
-- and capture enough other data to find where the calculation is incorrect.
SELECT Date,
DOW = DATENAME(dw,Date),
CalculatedISOWk = (DATEPART(dy,DATEDIFF(dd,0,Date)/7*7+3)+6)/7,
BuiltInISOWk = DATEPART(isowk,Date),
IsLeapYear = ISDATE(DATENAME(yy,Date)+'0229')
INTO #MyHead
FROM cteGenDates
;
--===== Find first or last weeks of each year that have an error.
SELECT *
FROM #MyHead
WHERE (DATEPART(dy,Date) <= 8 OR DATEPART(dy,Date) >= 356)
AND CalculatedISOWk <> BuiltInISOWk
ORDER BY Date
;
--Jeff Moden
Change is inevitable... Change for the better is not.