SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A Simple Formula to Calculate the ISO Week Number


A Simple Formula to Calculate the ISO Week Number

Author
Message
SwePeso
SwePeso
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4035 Visits: 3433
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.


N 56°04'39.16"
E 12°55'05.25"
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86520 Visits: 41098
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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86520 Visits: 41098
Ah... Missed it. You were talking about changing the BASE date of "0" to something else. I'll check on that, too1 Thanks again, Peter. I just sent an email to Steve and the WebMaster asking them to pull the article until I can come up with a fix (unless you beat me to it :-D).

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86520 Visits: 41098
Well, I found the problem. The problem is with anything that has a base date of less than 0 or "1900-01-01". Any correct calculations before then are simply a lucky shot it in the dark. The reason is the difference between dates produces a difference of days and not a date serial number. When the DATEPART(dy....) does its implicit conversion back to DATETIME, it's doing it based on that number of days rather than a true date serial number. I just so happens that the number of days matches the date serial number IF and only if the base date is 0 (1900-01-01) AND the date being converted is >= the base date.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86520 Visits: 41098
I still have some testing to do but it appears that converting the DATEDIFF back to a DATETIME using DATEADD instead of mistakenly allowing DATEPART to do it fixes all the problems. Here's the corrected formula. I'm using the base date of '1753' in this formula and it appears to be working correctly for all dates from 1753-01-01 through 9999-12-31. I still need to test it with the ranges of the "new" DATE datatype.

(DATEPART(dy,DATEADD(dd,DATEDIFF(dd,'1753',Date)/7*7+3 ,'1753'))+6)/7




--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
SwePeso
SwePeso
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4035 Visits: 3433
Any January 1st will do, as long as it is a monday in a non-leapyear.

I am running on SQL Server 2012 and have tested every January 1st between 0001 and 1900. Only some of them work, and the common denominator is that the working ones are non-leapyears.


N 56°04'39.16"
E 12°55'05.25"
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86520 Visits: 41098
SwePeso (7/26/2013)
Any January 1st will do, as long as it is a monday in a non-leapyear.

I am running on SQL Server 2012 and have tested every January 1st between 0001 and 1900. Only some of them work, and the common denominator is that the working ones are non-leapyears.


In the above, you tested the new formula or the old? Like I said, the old one was totally screwed for any date prior to 1900-01-01 and any semblance of actually working was purely accidental.

I got notice this morning that they took the article down as I requested. I'll have them put it back up after I make some changes.

Thanks again for finding the shortcoming, Peter. I appreciate it.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
SwePeso
SwePeso
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4035 Visits: 3433
Until Microsoft has fixed all date function to be DATE compliant, try this
CREATE FUNCTION dbo.fnISOWEEK
(
@Date DATE
)
RETURNS TINYINT
AS
BEGIN
RETURN (
SELECT CASE
WHEN nextYear <= theDate THEN 0
WHEN currYear <= theDate THEN (theDate - currYear) / 7
ELSE (theDate - prevYear) / 7
END + 1
FROM (
SELECT (CASE
WHEN prevYear % 400 = 0 THEN -366
WHEN prevYear % 100 = 0 THEN -365
WHEN prevYear % 4 = 0 THEN -366
ELSE -365
END + theDate - theDoY + 4) / 7 * 7 AS prevYear,
(theDate - theDoY + 4) / 7 * 7 AS currYear,
(CASE
WHEN currYear % 400 = 0 THEN 366
WHEN currYear % 100 = 0 THEN 365
WHEN currYear % 4 = 0 THEN 366
ELSE 365
END + theDate - theDoY + 4) / 7 * 7 AS nextYear,
theDate
FROM (
SELECT DATEPART(YEAR, @Date) - 1 AS prevYear,
DATEPART(YEAR, @Date) AS currYear,
DATEDIFF(DAY, '00010101', @Date) AS theDate,
DATEPART(DAYOFYEAR, @Date) AS theDoY
) AS d
) AS d
)
END




N 56°04'39.16"
E 12°55'05.25"
SwePeso
SwePeso
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4035 Visits: 3433
Or this, if you want to get rid of all Microsoft dependencies.
This will work for SQL 2000 and onwards, and on all dates between 00010101 and 99991231.
CREATE FUNCTION dbo.fnISOWEEK 
(
@Year SMALLINT,
@Month TINYINT,
@Day TINYINT
)
RETURNS TINYINT
AS
BEGIN
RETURN (
SELECT CASE
WHEN nextYearStart <= theDate THEN 0
WHEN currYearStart <= theDate THEN (theDate - currYearStart) / 7
ELSE (theDate - prevYearStart) / 7
END + 1
FROM (
SELECT (currJan4 - 365 - prevLeapYear) / 7 * 7 AS prevYearStart,
currJan4 / 7 * 7 AS currYearStart,
(currJan4 + 365 + currLeapYear) / 7 * 7 AS nextYearStart,
CASE @Month
WHEN 1 THEN @Day
WHEN 2 THEN 31 + @Day
WHEN 3 THEN 59 + @Day + currLeapYear
WHEN 4 THEN 90 + @Day + currLeapYear
WHEN 5 THEN 120 + @Day + currLeapYear
WHEN 6 THEN 151 + @Day + currLeapYear
WHEN 7 THEN 181 + @Day + currLeapYear
WHEN 8 THEN 212 + @Day + currLeapYear
WHEN 9 THEN 243 + @Day + currLeapYear
WHEN 10 THEN 273 + @Day + currLeapYear
WHEN 11 THEN 304 + @Day + currLeapYear
WHEN 12 THEN 334 + @Day + currLeapYear
END + currJan4 - 4 AS theDate
FROM (
SELECT CASE
WHEN (@Year - 1) % 400 = 0 THEN 1
WHEN (@Year - 1) % 100 = 0 THEN 0
WHEN (@Year - 1) % 4 = 0 THEN 1
ELSE 0
END AS prevLeapYear,
CASE
WHEN @Year % 400 = 0 THEN 1
WHEN @Year % 100 = 0 THEN 0
WHEN @Year % 4 = 0 THEN 1
ELSE 0
END AS currLeapYear,
365 * (@Year - 1) + (@Year - 1) / 400 - (@Year - 1) / 100 + (@Year - 1) / 4 + 3 AS currJan4
WHERE @Year BETWEEN 0 AND 9999
AND @Month BETWEEN 1 AND 12
AND @Day >= 1
AND 1 = CASE
WHEN @Month IN (1, 3, 5, 7, 8, 10, 12) AND @Day <= 31 THEN 1
WHEN @Month IN (4, 6, 9, 11) AND @Day <= 30 THEN 1
WHEN @Year % 400 = 0 AND @Day <= 29 THEN 1
WHEN @Year % 100 = 0 AND @Day <= 28 THEN 1
WHEN @Year % 4 = 0 AND @Day <= 29 THEN 1
WHEN @Day <= 28 THEN 1
ELSE 0
END
) AS d
) AS d
)
END




N 56°04'39.16"
E 12°55'05.25"
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86520 Visits: 41098
Peter. I've receive several emails from you. Have you received any of mine?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search