Isoweek in SQL Server 2005
Solution 1
Here's an approach that is similar to yours in that it also relies on this week's Thursday. But in the end it uses the date differently.
-
Get the date of this (ISO) week's Thursday.
Your own solution uses the hard coded date of a known Thursday. Alternatively, this week's Thursday could be found with the help of
@@DATEFIRST
:SELECT Th = DATEADD(DAY, 3 - (DATEPART(WEEKDAY, @date) + @@DATEFIRST - 2) % 7, @date)
(I wasn't struggling too much for the right formula because it was already known to me.)
-
Get the Thursday's day of year:
SELECT DY = DATEPART(DAYOFYEAR, Th)
-
Use the number to find out the week like this:
SELECT ISOWeek = (DY - 1) / 7 + 1
Here are the above calculations in a single statement:
SELECT ISOWeek = (DATEPART(DAYOFYEAR, Th) - 1) / 7 + 1
FROM (
SELECT Th = DATEADD(DAY, 3 - (DATEPART(WEEKDAY, @date) + @@DATEFIRST - 2) % 7, @date)
) s;
Solution 2
There is a link here for other earlier attempts http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60510
This is the OLD code for the function
CREATE function f_isoweek(@date datetime)
RETURNS INT
as
BEGIN
DECLARE @rv int
SELECT @rv = datediff(ww, dateadd(ww, datediff(d, 0, dateadd(yy, datediff(yy, 0, day4),3))/7,-4),day4)
FROM (SELECT dateadd(ww, datediff(day, 0, @date)/7, 3) day4) a
RETURN @rv
END
After combining @AndriyM 's brilliant answer with my own, we are down to 1 line. This is the NEW code.
CREATE function f_isoweek(@date datetime)
RETURNS INT
as
BEGIN
RETURN (datepart(DY, datediff(d, 0, @date) / 7 * 7 + 3)+6) / 7
-- replaced code for yet another improvement.
--RETURN (datepart(DY, dateadd(ww, datediff(d, 0, @date) / 7, 3))+6) / 7
END
Explanation for the old code (not going to explain the new code. It is fragments from my code and AndriyM's code):
Finding weekday 4 of the chosen date
dateadd(week, datediff(day, 0, @date)/7, 3)
Finding isoyear - year of weekday 4 of a week is always the same year as the isoyear of that week
datediff(yy, 0, day4)
When adding 3 days to the first day of the isoyear a random day of the first isoweek of the isoyear is found
dateadd(yy, datediff(yy, 0, day4),3)
finding relative week of the first isoweek of the isoyear
datediff(d, 0, dateadd(yy, datediff(yy, 0, day4),3))/7
Finding the monday minus 4 days of the first isoweek results in thursday of the week BEFORE the first day of the first isoweek of the isoyear
dateadd(ww, datediff(d, 0, dateadd(yy, datediff(yy, 0, day4),3))/7,-4)
Knowing first thursday of the week before the first isoweek and first thursday of the chosen week, makes it is quite easy to calculate the week, it doesn't matter which setting datefirst has since the weekdays of both dates are thursdays.
datediff(ww, dateadd(ww, datediff(d, 0, dateadd(yy, datediff(yy, 0, day4),3))/7,-4),day4)
Solution 3
Wow! Very good topic and solution to avoid using "set datefirst 1". I just want to add something. If like me, you also want to return the year with the ISO week, like "2015-01" as being "Year 2015, Week 01", it might be useful for reporting purpose. Since the year from ISO week can be different from the actual year of the date! Here is how I did in combination to your code.
DECLARE @Date AS DATETIME
SET @Date = '2014-12-31'
SELECT
CAST(CASE WHEN MONTH(@Date) = 1 AND Q.ISOweek > 50 THEN YEAR(@Date) - 1
WHEN MONTH(@Date) = 12 AND Q.ISOweek < 3 THEN YEAR(@Date) + 1
ELSE YEAR(@Date)
END
AS VARCHAR(4))
+ '-'
+ RIGHT('00' + CAST(Q.ISOweek AS NVARCHAR(2)), 2) AS ISOweek
FROM (SELECT (datepart(DY, datediff(d, 0, @Date) / 7 * 7 + 3) + 6) / 7 AS ISOweek) Q
Will return "2015-01".
![t-clausen.dk](https://i.stack.imgur.com/e0Qyw.jpg?s=256&g=1)
Comments
-
t-clausen.dk about 2 years
In SQL Server 2008 the isoweek can be found with this:
SELECT datepart(iso_week, getdate())
Before SQL Server 2008 there were no built-in function to find isoweek.
I have been searching high and low for a good syntax to find a userdefined iso_week for SQL Server 2005. I found quite a few solutions. Didn't like any of the solutions I found, most of them didn't work, and they were way too long.
Since the issue is very old, I would expect this issue is depleted and the best solution has been found. I was unable to find a good method though.
I wrote a solution, which I am going to post later. But before I do, I want to make absolutely sure that no one else can match the solution I wrote.
I am hoping to earn the self-learner badge. I urge people to find the best answers out there for this ancient question.
I am going to post my answer after giving people a chance of finding a good solution.
-
Mikael Eriksson almost 13 years+1. Compared to this version msdn.microsoft.com/en-us/library/ms186755.aspx yours are multitudes better because it is not dependent on
set datefirst 1
. -
t-clausen.dk almost 13 years+1 awsome, took me a while to realize what you were doing, so far I decided you are cheating, why didn't i think of that. I combined our answers to a very short compact solution.
-
Andriy M almost 13 yearsNice! And I like your
(n + 6) / 7
better than my(n - 1) / 7 + 1
. Would have upvoted this again, if I could. -
t-clausen.dk about 9 yearsI already wrote a simple script for iso_year. stackoverflow.com/questions/22829604/…
-
Martin over 8 yearsWhat if i want my weeks to start on Sunday?
-
t-clausen.dk over 8 years@Martin iso weeks does not start on a sunday
-
MDB over 7 yearsThis formula is awesome. Thanks a lot!
-
OmGanesh almost 7 years@Martin, i need to use the same formula concept for finding epiweek (cmmcp.org/epiweek.htm) which is slightly different as it starts on sunday; I just changed the choosing date as '1905-01-01' instead of '1900-01-01'~0 ; will that work as per your details... SELECT (DATEPART(day,DATEDIFF(day,'1905-01-01',@day)/7*7+3)+6)/7
-
t-clausen.dk almost 4 yearsHi @AndriyM you will probably be one of the few people who can appreciate this - imagine 8 days a week and using the same logic
-
Andriy M almost 4 years@t-clausen.dk: Still figuring out all the details of the logic but that's incredible. Thanks for sharing!
-
t-clausen.dk almost 4 yearsI should warn you, some of the answers I provided for code golf are not easy to understand
-
Andriy M almost 4 years@t-clausen.dk: That's quite all right. When I fail to understand any of them, I know who to ask for help :) Cheers!