SQLServerCentral / Discuss Content Posted by Christoffer Hedgate / Article Discussions / Article Discussions by Author / On the Trail of the ISO Week / Latest Posts

RE: On the Trail of the ISO Week

Michael Valentine Jones — Thu, 15 Sep 2011 14:01:52 GMT

This one is fairly short and I did a lot of testing to verify it works OK:ISO Week of Year Function[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60510[/url]This has the week and day of week included:ISO Year Week Day of Week Function[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60515[/url]Returns the ISO 8601 Year Week Day of Week in format YYYY-W01-D for the date passed.There are a number of columns with ISO week support in this date table function:Date Table Function F_TABLE_DATE:[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519[/url]ISO_YEAR_WEEK_NO - ISO 8601 year and week in format YYYYWWExample = 200403ISO_WEEK_NO - ISO 8601 week of year in format WWExample = 52ISO_DAY_OF_WEEK - ISO 8601 Day of week number, Mon=1, Tue=2, Wed=3, Thu=4, Fri=5, Sat=6, Sun=7ISO_YEAR_WEEK_NAME - ISO 8601 year and week in format YYYY-WNNExample = 2004-W52ISO_YEAR_WEEK_DAY_OF_WEEK_NAME - ISO 8601 year, week, and day of week in format YYYY-WNN-DExample = 2004-W52-2Edit: Didn't see that this is an old thread and that I had already posted on it. :doze:

RE: On the Trail of the ISO Week

geoff5 — Thu, 15 Sep 2011 13:52:33 GMT

I've been struggling through the logic of ISO 8601 myself. My solution does not look like anything I've seen in this thread yet, so I am posting my solution for consideration. The approach I took boils down to the following steps: 1. Let [b]TargetDate [/b]equal the date for which we want to know the ISO week value. 2. Let [b]CurrentThursday [/b]equal the date of the Thursday associated with the same week as the TargetDate (taking into account that Sunday belongs with the prior Thursday). 3. Let [b]FirstThursday [/b]equal the date of the Thursday of the week containing January 4th, basing that on the year value of the CurrentThursday (not the TargetDate) and still taking into account that Sunday belongs with the prior Thursday. (Why January 4th? January 4th is [i]always[/i] in the first ISO week of the year, which is not true for January 1st.) 4. Let [b]ISO_Week_Value[/b] equal the difference, in weeks, between the FirstThursday and the CurrentThursday, plus one.Here is a query that uses variables to reflect each step of the algorithm and includes two AllInOne versions that rely on only the [b]@TargetDate[/b] variable and no others. The @DateOffset variable is used to make sure that Monday - Wednesday are always in the same week as the following Thursday and that Friday - Sunday are always in the same week as the prior Thursday no matter what the @@datefirst value is. The [b]@ThursdayOffset[/b] variable is used to find Thursday no matter what the @@datefirst value is. [code="sql"]declare @DateOffset as smallint, @ThursdayOffset as smallint, @TargetDate as datetime, @CurrentThursday as datetime, @FirstThursday as datetime, @ISO_WeekNumber as tinyintset @DateOffset = case when @@datefirst > 4 then @@datefirst - 8 when @@datefirst > 1 then @@datefirst - 1 else 0 endset @ThursdayOffset = case when @@datefirst < 5 then 5 - @@datefirst else 12 - @@datefirst endset @TargetDate = getdate()set @CurrentThursday = dateadd(day, @ThursdayOffset - datepart(dw, @TargetDate + @DateOffset), @TargetDate + @DateOffset)set @FirstThursday = dateadd(day, @ThursdayOffset - datepart(dw, cast('1/4/' + datename(year, @CurrentThursday) as datetime) + @DateOffset), cast('1/4/' + datename(year, @CurrentThursday) as datetime) + @DateOffset)set @ISO_WeekNumber = datediff(week, @FirstThursday, @CurrentThursday) + 1select TestDate = @TargetDate, CurrentThursday = @CurrentThursday, FirstThursday = @FirstThursday, ISO_WeekNumber = @ISO_WeekNumber, ISO_WeekYear = year(@CurrentThursday), ISO_WeekNumber_AllInOne = datediff(week, dateadd(day, case when @@datefirst < 5 then 5 - @@datefirst else 12 - @@datefirst end - datepart(dw, cast('1/4/' + datename(year, dateadd(day, case when @@datefirst < 5 then 5 - @@datefirst else 12 - @@datefirst end - datepart(dw, @TargetDate + case when @@datefirst > 4 then @@datefirst - 8 when @@datefirst > 1 then @@datefirst - 1 else 0 end), @TargetDate + case when @@datefirst > 4 then @@datefirst - 8 when @@datefirst > 1 then @@datefirst - 1 else 0 end)) as datetime) + case when @@datefirst > 4 then @@datefirst - 8 when @@datefirst > 1 then @@datefirst - 1 else 0 end), cast('1/4/' + datename(year, dateadd(day, case when @@datefirst < 5 then 5 - @@datefirst else 12 - @@datefirst end - datepart(dw, @TargetDate + case when @@datefirst > 4 then @@datefirst - 8 when @@datefirst > 1 then @@datefirst - 1 else 0 end), @TargetDate + case when @@datefirst > 4 then @@datefirst - 8 when @@datefirst > 1 then @@datefirst - 1 else 0 end)) as datetime) + case when @@datefirst > 4 then @@datefirst - 8 when @@datefirst > 1 then @@datefirst - 1 else 0 end), dateadd(day, case when @@datefirst < 5 then 5 - @@datefirst else 12 - @@datefirst end - datepart(dw, @TargetDate + case when @@datefirst > 4 then @@datefirst - 8 when @@datefirst > 1 then @@datefirst - 1 else 0 end), @TargetDate + case when @@datefirst > 4 then @@datefirst - 8 when @@datefirst > 1 then @@datefirst - 1 else 0 end)) + 1, ISO_WeekYear_AllInOne = year(dateadd(day, case when @@datefirst < 5 then 5 - @@datefirst else 12 - @@datefirst end - datepart(dw, @TargetDate + case when @@datefirst > 4 then @@datefirst - 8 when @@datefirst > 1 then @@datefirst - 1 else 0 end), @TargetDate + case when @@datefirst > 4 then @@datefirst - 8 when @@datefirst > 1 then @@datefirst - 1 else 0 end))[/code]

RE: On the Trail of the ISO Week

R.P.Rozema — Mon, 15 Aug 2011 01:42:13 GMT

I needed the iso week number too in SQL 2005. Looked for it in all the usual places (bol, forums, google, etc) and found many different implementations. Most however have shortcomings. Usually a combination of one or more of:- using date string manipulations (dependent on locale) or- dependent on datefirst setting or- implemented procedural.It must be possible to do better than that. So this is what I wrote. The following query calculates for a given (set of) date(s), the iso week number plus it's iso year. And it does not use any string manipulations/conversions, it is independent of datefirst and it can be put into a single select statement, a view or a cte. Even though SQL 2008 now supports datepart(isowk, ) which does the same thing, I hope someone will still find it useful for older versions:[code="sql"]select d.date ,case when d.date < x.thisfirstmonday then datepart(year, x.prevjan4) when d.date >= x.nextfirstmonday then datepart(year, x.nextjan4) else datepart(year, x.thisjan4) end as isoyear ,case when d.date < x.thisfirstmonday then 1 + datediff(day, x.prevfirstmonday, d.date) / 7 when d.date >= x.nextfirstmonday then 1 else 1 + datediff(day, x.thisfirstmonday, d.date) / 7 end isoweeknumberfrom ( -- To demonstrate show week numbers for 14 days around today's date, -- Januari 1st and December 31st. select dateadd(day, 7 - t.n, dt.date) as date from ( select dateadd(day, datediff(day, 0, getdate()), 0) as date union select dateadd(year, datediff(year, 0, getdate()), 0) union select dateadd(day, -1, dateadd(year, 1 + datediff(year, 0, getdate()), 0)) ) dt cross join (select 1 as n union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10 union all select 11 union all select 12 union all select 13 union all select 14 ) t ) d cross apply ( select max(case t.n when -1 then x1.jan4 else 0 end) as prevjan4 ,max(case t.n when 0 then x1.jan4 else 0 end) as thisjan4 ,max(case t.n when 1 then x1.jan4 else 0 end) as nextjan4 ,max(case t.n when -1 then x2.jan4weekday else 0 end) as prevjan4weekday ,max(case t.n when 0 then x2.jan4weekday else 0 end) as thisjan4weekday ,max(case t.n when 1 then x2.jan4weekday else 0 end) as nextjan4weekday ,max(case t.n when -1 then x3.firstmonday else 0 end) as prevfirstmonday ,max(case t.n when 0 then x3.firstmonday else 0 end) as thisfirstmonday ,max(case t.n when 1 then x3.firstmonday else 0 end) as nextfirstmonday from ( select -1 as n union all select 0 union all select 1 ) t cross apply ( select dateadd(year, t.n + datediff(year, 0, d.date), dateadd(day, 3, 0)) as jan4 ) x1 cross apply ( select (-2 + datepart(dw, x1.jan4) + @@datefirst) % 7 + 1 as jan4weekday ) x2 cross apply ( select dateadd(day, 1 - x2.jan4weekday, x1.jan4) as firstmonday ) x3 ) xorder by 1;[/code]

RE: On the Trail of the ISO Week

Michael Valentine Jones — Mon, 22 Mar 2010 12:34:05 GMT

[quote][b]Jeff Moden (3/22/2010)[/b][hr]Lookup CREATE FUNCTION in Books Online... Example "A" is a nice, short function that determines ISO week. It could probably be optimized to be an INLINE function for performance reasons or might be able to be optimized to simply be a formula instead of a function, but it's a whole lot shorter and easier to understand than some of the example code I've seen on this thread.[/quote]The function in BOL depends on the setting of DATEFIRST, so it isn't all that useful as a universal function.This is a bit longer, but does not depend on the setting of DATEFIRST:ISO Week of Year Function[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60510[/url]

RE: On the Trail of the ISO Week

Jeff Moden — Mon, 22 Mar 2010 11:28:48 GMT

Lookup CREATE FUNCTION in Books Online... Example "A" is a nice, short function that determines ISO week. It could probably be optimized to be an INLINE function for performance reasons or might be able to be optimized to simply be a formula instead of a function, but it's a whole lot shorter and easier to understand than some of the example code I've seen on this thread.

RE: On the Trail of the ISO Week

karol63 — Mon, 22 Mar 2010 08:41:37 GMT

Perfect, thanky you.It helped me a lot.

RE: On the Trail of the ISO Week

Adrian Peakman — Thu, 09 Oct 2008 08:48:15 GMT

Yes... mine work (see earlier in the thread)print dbo.fnISOYearWeek('Aug 31 2008')Gives 200835 :D

RE: On the Trail of the ISO Week

ms-876980 — Thu, 09 Oct 2008 07:22:49 GMT

Hi, I've found this ISO-Week function everywhere on the NET, but It isn't correct. Try with 2008.08.31. It must be week35 instead of week36. Has anybody in the world a right fuction for this ????

RE: On the Trail of the ISO Week

unisense_nl — Tue, 13 Feb 2007 09:32:00 GMT

Maybe, other people like this functionGOCREATE FUNCTION [dbo].[GetDateTimeOfIsoWeek](@WeekYearNumber int, @DayNumber int)RETURNS datetimeAS-------------------------------------------------------------------------------- GetDateTimeOfIsoWeek---- According to ISO 8601, the first week of the year (week 1) is the week -- that contains at least the first four days of the year. In other words, -- the week that contains the first Thursday of a year is also week 1 of that year.-- Also note that according to ISO 8601 a week always starts on a Monday.-- if 01-01=Monday then 01-04=Thursday -> 01-01=week 1 AND 01-04=week 1 -- if 01-01=Thuesday then 01-04=Friday -> 01-01=week 1 AND 01-04=week 1 -- if 01-01=Wednesday then 01-04=Saterday -> 01-01=week 1 AND 01-04=week 1 -- if 01-01=Thursday then 01-04=Sunday -> 01-01=week 1 AND 01-04=week 1 -- if 01-01=Friday then 01-04=Monday -> 01-01=week 52 AND 01-04=week 1 -- if 01-01=Saterday then 01-04=Thuesday -> 01-01=week 52 AND 01-04=week 1 -- if 01-01=Sunday then 01-04=Wednesday-> 01-01=week 52 AND 01-04=week 1 ---- 4th jan. of indicated year is always in week 1-- week 1 always starts on Monday, just like other weeks-- 4th jan. must be Thursday, Friday, Saterday or Sunday if 01-01 is in week 1-- 1th jan. must be Monday, Thuesday, Wednesday or Thursday if 01-01 is in week 1---------------------------------------------------------------------------------- Created On: 19 december 2006-- Created By: martijn Schuurmans-- Variables:-- * @WeekYearNumber:-- like 200752, 200453, etc.-- * @DayNumber: -- 1 = Monday-- 2 = Thuesday-- 3 = Wednesday-- 4 = Thursday-- 5 = Friday-- 6 = Saterday-- 7 = Sunday------------------------------------------------------------------------------BEGIN -- Declare the return variable here DECLARE @DayId int DECLARE @WeekNumber int DECLARE @YearNumber int DECLARE @FunctionResult datetime

-- validate input, we don't like negative WeekYear numbers SET @WeekYearNumber = ABS(@WeekYearNumber) IF @DayNumber < 1 SET @DayNumber = 1

IF @DayNumber > 7 SET @DayNumber = 7

-- get WeekId without Year Number SET @WeekNumber = right(@WeekYearNumber, 2)

-- get YearId from @YearNumber SET @YearNumber = CASE -- like: 52 WHEN @WeekYearNumber < 100 THEN YEAR(GETDATE()) -- like: 0752 WHEN @WeekYearNumber < 10000 THEN (LEFT(YEAR(GETDATE()),2)*100) + left(@WeekYearNumber, 2) -- like: 200752 ELSE LEFT(@WeekYearNumber, 4) END

-- get datevalue of monday in week 1 of indicated year -- get datevalue of 1th jan. of indicated year SET @FunctionResult = CAST((CAST(@YearNumber AS char(4))+ '0101') AS datetime) -- determine day of week for 01-01-YearNumber -- make corrections for server settings (=@@DATEFIRST) SET @DayId = DATEPART(dw, @FunctionResult) + @@DATEFIRST - 1 IF @DayId > 7 SET @DayId = @DayId - 7

-- determine the correction that is needed on 01-01-YearNumber day to get to Monday of week 1 -- @DayId: 1=Monday; 2=Thuesday; 3=Wednesday; 4=Thursday; 5=Friday; 6=Saterday; 7=Sunday SET @DayId = CASE @DayId WHEN 1 THEN 0 -- if weekday of 01-01=Monday then: no correction WHEN 2 THEN -1 -- if weekday of 01-01=Thuesday then: Monday of W1 is 1 day before 01-01 WHEN 3 THEN -2 -- if weekday of 01-01=Wednesday then: Monday of W1 is 2 days before 01-01 WHEN 4 THEN -3 -- if weekday of 01-01=Thursday then: Monday of W1 is 3 days before 01-01 WHEN 5 THEN 3 -- if weekday of 01-01=Friday then: Monday of W1 is 3 days after 01-01 WHEN 6 THEN 2 -- if weekday of 01-01=Saterday then: Monday of W1 is 2 days after 01-01 WHEN 7 THEN 1 -- if weekday of 01-01=Sunday then: Monday of W1 is 1 day after 01-01 END

-- this is datevalue of monday in week 1 of indicated year SET @FunctionResult = DATEADD(dd, @DayId, @FunctionResult) -- get last day of week, this is always sunday -- and make correction for given @DayNumer SET @FunctionResult = DATEADD(dd, ((@WeekNumber * 7) + @DayNumber - 8), @FunctionResult)

RETURN(@FunctionResult)END

RE: On the Trail of the ISO Week

Ian Cox-160558 — Thu, 13 Jul 2006 08:02:00 GMT

I like your method Tomasz, it leads to much cleaner looking queries. However, your code doesn't quite work.In particular you are subtracting 1 from the day of the week twiceThis modified version worksCREATE FUNCTION dbo.fnISOweek(@date datetime)RETURNS intASBEGINdeclare @dayOfWeek intdeclare @1ThISOWeek1ThDay datetimedeclare @4ThJan datetimedeclare @1StJan datetimedeclare @31StDec datetimedeclare @numberOfISOweeks intdeclare @tmp intdeclare @returnValue as intset @4ThJan = cast(CAST(YEAR(@date) AS CHAR(4)) + '0104' as datetime)set @1StJan = cast(CAST(YEAR(@date) AS CHAR(4)) + '0101' as datetime)set @31StDec = cast(CAST(YEAR(@date) AS CHAR(4)) + '1231' as datetime)--"rule of thursday"if (DATEPART(dw, @1StJan) = 5 or DATEPART(dw, @31StDec) = 5 )set @numberOfISOweeks = 53elseset @numberOfISOweeks = 52set @dayOfWeek = DATEPART(dw, @4ThJan) set @1ThISOWeek1ThDay = DATEADD(day, -(@dayOfWeek -1) ,@4ThJan)if @date < @1ThISOWeek1ThDayBEGINif (DATEPART(dw, DATEADD(year,-1, @1StJan)) = 5 or DATEPART(dw,DATEADD(year,-1, @31StDec)) = 5 ) set @returnValue = 53else set @returnValue = 52return @returnValueEND--number of days between 1th day of the 1th ISO week and Jan 1set @tmp = (select DATEDIFF(day,@1StJan , @1ThISOWeek1ThDay)) + 1set @returnValue = (DATEPART(dy,@date)- @tmp ) / 7 + 1-- if number of week is greater than number of ISO weeks in current year it means-- that the date is in the first week of next yearif @returnValue > @numberOfISOweeksset @returnValue = 1 -- or -1 as an errorreturn @returnValueEND

RE: On the Trail of the ISO Week

Chris Hedgate — Mon, 23 Jan 2006 05:51:00 GMT

Where does it say ISO 6801 in the article? I can't find any references to it. I know I had an error a long time ago, but that was changed to 8601.Re: the setting of DATEFIRST. Note this section from my article:First you should note that whenever the day of the week is checked for a specific date, we always make sure that the ISO standard of using Monday as the starting day of the week is used, regardless of the current setting of @@datefirst. This is accomplished by adding @@datefirst - 1 to the value returned by DATEPART(dw, ) and then dividing this number by 7. The remainder of the division is the day of the week for the specified date using Monday as the starting day of the week. Note though that Sunday becomes 0 with this expression, so if you need it to be 7 (for some calculation or whatever) you must exchange it for that.Finally, regarding performance, the fastest way I have found to get ISO 8601 week numbers is to use the built-in routines in Visual Basic.NET. Creating a user-defined function in Visual Basic.NET is very easy, so if you're running SQL Server 2005 that is an option.

RE: On the Trail of the ISO Week

Bill the Cat — Fri, 20 Jan 2006 08:04:00 GMT

FYI, the article sites ISO6801, I believe the author is referring to ISO8601, and I have certainly run into this same kind of issue in respect to ensuring applications developed in different environments are compatible with 8601 defined week numbers and weekday numbers.

RE: On the Trail of the ISO Week

Adrian Peakman — Fri, 20 Jan 2006 02:54:00 GMT

Incidentally the routine posted by rkintrup fails for me every few years:Can't test Tomasz's routine as if fails on my installation (British dates). I'd be interested to know if my routines fail on other installations than British, hopefully they shouldn't! (erk!)Oh, the use of DATEPART(dw) is sensitive to the value of DATEFIRST. (I think everyone's routines are affected by this... )My routines are coded to use the ISO Standard of Monday as the first day of the week (SET DATEFIRST 1). US installation is DATEFIRST 7 (Sunday) by default I believe. They can easily be altered to conform to other requirements.Thanks,AdrianDate fnISOWeek Rkintrup002/01/1970 1 5309/01/1970 2 5416/01/1970 3 5523/01/1970 4 5630/01/1970 5 5706/02/1970 6 5813/02/1970 7 5920/02/1970 8 6027/02/1970 9 6106/03/1970 10 6213/03/1970 11 6320/03/1970 12 6402/01/1976 1 5309/01/1976 2 5416/01/1976 3 5523/01/1976 4 5630/01/1976 5 5706/02/1976 6 5813/02/1976 7 5920/02/1976 8 6027/02/1976 9 6105/03/1976 10 6212/03/1976 11 6319/03/1976 12 6402/01/1981 1 5309/01/1981 2 5416/01/1981 3 5523/01/1981 4 5630/01/1981 5 5706/02/1981 6 5813/02/1981 7 5920/02/1981 8 6027/02/1981 9 6106/03/1981 10 6213/03/1981 11 6320/03/1981 12 6402/01/1987 1 5309/01/1987 2 5416/01/1987 3 5523/01/1987 4 5630/01/1987 5 5706/02/1987 6 5813/02/1987 7 5920/02/1987 8 6027/02/1987 9 6106/03/1987 10 6213/03/1987 11 6320/03/1987 12 6402/01/1998 1 5309/01/1998 2 5416/01/1998 3 5523/01/1998 4 5630/01/1998 5 5706/02/1998 6 5813/02/1998 7 5920/02/1998 8 6027/02/1998 9 6106/03/1998 10 6213/03/1998 11 6320/03/1998 12 6402/01/2004 1 5309/01/2004 2 5416/01/2004 3 5523/01/2004 4 5630/01/2004 5 5706/02/2004 6 5813/02/2004 7 5920/02/2004 8 6027/02/2004 9 6105/03/2004 10 6212/03/2004 11 6319/03/2004 12 6402/01/2009 1 5309/01/2009 2 5416/01/2009 3 5523/01/2009 4 5630/01/2009 5 5706/02/2009 6 5813/02/2009 7 5920/02/2009 8 6027/02/2009 9 6106/03/2009 10 6213/03/2009 11 6320/03/2009 12 6402/01/2015 1 5309/01/2015 2 5416/01/2015 3 5523/01/2015 4 5630/01/2015 5 5706/02/2015 6 5813/02/2015 7 5920/02/2015 8 6027/02/2015 9 6106/03/2015 10 6213/03/2015 11 6320/03/2015 12 64

RE: On the Trail of the ISO Week

Adrian Peakman — Fri, 20 Jan 2006 02:09:00 GMT

Hi,The ISO Week problem arised last year for us. I programmed, tested and benchmarked a set of various conversion routines. Speed was essential in my case.Above routine: 1360ms for 10000 conversionsfnISOWeek: 513ms for 10000 conversionsIn general I first group results by SQL Year/Week then convert the grouped results using fnISOWeekFromSQL (very fast). Finally I regroup, as SQL separates the end and beginning weeks over a year end. This is faster in my case (millions of rows) than converting every row, especially in crosstabs.I thought I'd share them, though please do credit me if you use them!: fnISOWeek (@datetime) returns ISO week as an integer from SQL datetimefnISOYear (@datetime) returns ISO Year as an integer from SQL datetimefnISOYearWeek (@datetime) Returns the ISO Year Week in YYYYWW format of the given SQL date (useful for ordering data)fnISOWeekFromSQL (@week, @year) returns ISO week given the SQL DATEPART week/year fnISOYearWeekFromSQL (@week, @year) Returns the ISO Year Week in YYYYWW format given the SQL DATEPART Year and WeekfnISOWeekStartDate (@week, @year) Returns the date of the first day of the given ISO Week/Year (Monday)As we're talking date functions, I also have a very fast midnight calculation if anybody is interested (calculates "12/1/2006 00:00:00" from "12/1/2006 xx:xx:xx")Adrian PeakmanPS (Added)Oh, the use of DATEPART(dw) is sensitive to the value of DATEFIRST. (I think everyone's routines are affected by this... )My routines are coded to use the ISO Standard of Monday as the first day of the week (SET DATEFIRST 1). US installation is DATEFIRST 7 (Sunday) by default I believe. They can easily be altered to conform to other requirements.---------------------------------------CREATE FUNCTION fnISOWeek--Adrian Peakman 6/12/2004--Returns the ISO Week of the given date(@d datetime)returns intASBEGINDECLARE @ISOWeek intSET @ISOWeek= DATEPART(wk,@d)IF DATEPART(dw,CAST(DATEPART(Year,@d) as varchar)) >4 begin SET @ISOWeek=@ISOWeek-1 IF @ISOWeek=0 begin SET @d = DATEADD(day,-3,@d) SET @ISOWeek= DATEPART(wk,@d) IF DATEPART(dw,CAST(DATEPART(Year,@d) as varchar)) >4 SET @ISOWeek=@ISOWeek-1 end endIF @ISOWeek=53 IF (DATEPART(dd,@d)-DATEPART(dw,@d))>= 28 SET @ISOweek=1Return (@ISOWeek)END--------------------------------CREATE FUNCTION fnISOYear--Adrian Peakman 6/12/2004--Returns the ISO Year of the given date(@d datetime)returns intASBEGINDECLARE @ISOWeek intDECLARE @iDayOfYear intDECLARE @iYear intSET @iDayOfYear=DATEPART(dy,@d)SET @iYear=DATEPART(Year,@d)IF @iDayOfYear > 3 AND @iDayOfYear < 363 RETURN(@iYear)--Okay now this date may be part of the current year, the last year or the next year!SET @ISOWeek= dbo.fnISOWeek(@d)IF @iDayOfYear<10 begin --Start of Year IF @ISOWeek=1 RETURN(@iYear) RETURN(@iYear-1) end--End Of YearIF @ISOWeek=1 RETURN(@iYear+1)RETURN(@iYear)END---------------------CREATE FUNCTION fnISOYearWeek--Adrian Peakman 6/12/2004--Returns the ISO Year Week in YYYYWW format of the given date ( @d datetime )RETURNS intAsBEGINDECLARE @ISOWeek int, @ISOYear intSET @ISOWeek= DATEPART(wk,@d)SET @ISOYear= DATEPART(Year,@d)IF DATEPART(dw,CAST(@ISOYear as varchar)) >4 begin SET @ISOWeek=@ISOWeek-1 IF @ISOWeek=0 begin --SET @ISOWeek=dbo.fnISOWeek(DATEADD(day,-3,@d)) SET @d = DATEADD(day,-3,@d) SET @ISOWeek= DATEPART(wk,@d) IF DATEPART(dw,CAST(DATEPART(Year,@d) as varchar)) >4 SET @ISOWeek=@ISOWeek-1 IF @ISOWeek >0 SET @ISOYear=@ISOYear-1 end endIF @ISOWeek=53 IF (DATEPART(dd,@d)-DATEPART(dw,@d))>= 28 BEGIN SET @ISOweek=1 SET @ISOYear=@ISOYear+1 ENDRETURN(@ISOYear*100+@ISOWeek) END-------------------------------CREATE FUNCTION fnISOWeekFromSQL--Adrian Peakman 6/12/2004--Returns the ISO Week given the SQL Week ( @IntWeekNumber int, @IntYear int )RETURNS intAsBEGINDECLARE @ISOWeek intDECLARE @Jan1Day datetimeSET @Jan1Day=DATEPART(dw,CONVERT(varchar,@IntYear))SET @ISOWeek=@IntWeekNumberIF @Jan1Day>4 SET @ISOWeek=@ISOWeek-1IF @ISOWeek>52 begin IF DATEPART(dw,CONVERT(varchar,@IntYear+1))<5 SET @ISOWeek=1 end IF @ISOWeek>0 RETURN(@IsoWeek)SET @ISOWeek=53IF DATEPART(dw,CONVERT(varchar,@IntYear-1)) >4 SET @ISOWeek=@ISOWeek-1RETURN(@ISOWeek) END---------------------------------CREATE FUNCTION fnISOYearWeekFromSQL--Adrian Peakman 6/12/2004--Returns the ISO Year Week in YYYYWW format given the SQL DATEPART Year and Week ( @intWeekNumber int, @intYear int )RETURNS intAsBEGINDECLARE @ISOWeek intDECLARE @ISOYear intDECLARE @Jan1Day datetimeSET @Jan1Day=DATEPART(dw,CONVERT(varchar,@intYear))SET @ISOWeek=@intWeekNumberSET @ISOYear=@intYearIF @Jan1Day>4 SET @ISOWeek=@ISOWeek-1IF @ISOWeek>52 begin IF DATEPART(dw,CONVERT(varchar,@intYear+1))<5 begin SET @ISOWeek=1 SET @ISOYear=@ISOYear+1 end end IF @ISOWeek>0 RETURN(@ISOYear*100+@IsoWeek)SET @ISOWeek=53SET @ISOYear=@ISOYear-1IF DATEPART(dw,CONVERT(varchar,@IntYear-1)) >4 begin SET @ISOWeek=@ISOWeek-1 endRETURN(@ISOYear*100+@ISOWeek) END----------------------------------CREATE FUNCTION fnISOWeekStartDate--Adrian Peakman 6/12/2004--Returns the date of the first day of the given ISO Week/Year (Monday) ( @IntWeekNumber int, @IntYear int )RETURNS DateTimeAsBEGINDECLARE @StartDate DateTimeDECLARE @Jan1Day intSET @StartDate=CONVERT(varchar,@IntYear)SET @Jan1Day=DATEPART(dw,@StartDate)IF @Jan1Day>4 Set @StartDate=DATEADD(day,1-@Jan1Day+7*@IntWeekNumber,@StartDate)ELSE Set @StartDate=DATEADD(day,1-@Jan1Day+7*(@IntWeekNumber-1),@StartDate)RETURN(@StartDate) END

RE: On the Trail of the ISO Week

Jeff Moden — Sat, 27 Aug 2005 12:21:00 GMT

Better late than never, I guess...

DECLARE @Year CHAR(4) DECLARE @NextYear CHAR(4)DECLARE @ISOWeek VARCHAR(2)

SET @Year = '2001' SET @ISOWeek = '53' SET @NextYear = @Year+1 SELECT CASEWHEN @ISOWeek > 0 AND DATEADD(wk,DATEDIFF(wk,0,'01/04/'+@Year),0)+((@ISOWeek-1)*7) < DATEADD(wk,DATEDIFF(wk,0,'01/04/'+@NextYear),0)THEN DATEADD(wk,DATEDIFF(wk,0,'01/04/'+@Year),0)+((@ISOWeek-1)*7)ELSE 0 END AS StartDate, DATEADD(wk,DATEDIFF(wk,0,'01/04/'+@Year),0)+((@ISOWeek)*7)-1 AS EndDate

--todo... needs some error checking for the week number...

It returns a "0" or 01/01/1900 for the start date if the week was not found for the year...

I'm thinking that there's no ISO week 53 in 2001 but I might be using the wrong ISO "standard".

RE: On the Trail of the ISO Week

requena — Mon, 18 Jul 2005 03:17:00 GMT

Sombody now how to calculate first monday day from given week

For example I need get the day from a given week:

ISO date ‘20050101’ is the week 53 in ISO 8601

If a have the week number 53 how I can calculate the ISO date?

Thank for all in advance.

RE: On the Trail of the ISO Week

Tomasz Kubacki — Mon, 06 Jun 2005 09:22:00 GMT

Hmmm that is my function similiar to above but IMHO easier to understandCREATE FUNCTION ISOweek(@date datetime)RETURNS intASBEGINdeclare @dayOfWeek intdeclare @1ThISOWeek1ThDay datetimedeclare @4ThJan datetimedeclare @1ThJan datetimedeclare @31ThDec datetimedeclare @numberOfISOweeks intdeclare @tmp intdeclare @returnValue as intset @4ThJan = cast(CAST(YEAR(@date) AS CHAR(4)) + '-01-04' as datetime)set @1ThJan = cast(CAST(YEAR(@date) AS CHAR(4)) + '-01-01' as datetime)set @31ThDec = cast(CAST(YEAR(@date) AS CHAR(4)) + '-12-31' as datetime)--"rule of thursday"if (DATEPART(dw, @1ThJan) = 5 or DATEPART(dw, @31ThDec) = 5 )set @numberOfISOweeks = 53elseset @numberOfISOweeks = 52set @dayOfWeek = (select DATEPART(dw, @4ThJan) - 1)if @dayOfWeek = 0 set @dayOfWeek = 7set @1ThISOWeek1ThDay = (select DATEADD(day, -(@dayOfWeek -1) ,@4ThJan))if @date < @1ThISOWeek1ThDay BEGIN if (DATEPART(dw, DATEADD(year,-1, @1ThJan)) = 5 or DATEPART(dw,DATEADD(year,-1, @31ThDec)) = 5 ) set @returnValue = 53 else set @returnValue = 52 return @returnValue -- or -1 as an error END--number of days between 1th day of the 1th ISO week and Jan 1set @tmp = (select DATEDIFF(day,@1ThJan , @1ThISOWeek1ThDay)) + 1set @returnValue = (DATEPART(dy,@date)- @tmp ) / 7 + 1 -- if number of week is greater than number of ISO weeks in current year it means -- that the date is in the firth week of next yearif @returnValue > @numberOfISOweeksset @returnValue = 1 -- or -1 as an errorreturn @returnValueEND

RE: On the Trail of the ISO Week

rkintrup — Tue, 26 Apr 2005 03:46:00 GMT

Here is a version which uses CONVERT to allow using the yyyy-mm-dd format. This makes the code independent from SQL Server installation flavors.Ralf-----DECLARE @date datetimeSET @date = CONVERT(datetime, '2003-12-31', 120) -- 1--SET @date = CONVERT(datetime, '2005-01-01', 120) -- 53--SET @date = CONVERT(datetime, '2005-01-05', 120) -- 1--SET @date = CONVERT(datetime, '2006-01-01', 120) -- 52SELECT @date,CASE -- Exception where @date is part of week 52 (or 53) of the previous year WHEN @date < CASE (DATEPART(dw, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120)) + @@DATEFIRST - 1) % 7 WHEN 1 THEN CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120) WHEN 2 THEN DATEADD(d, -1, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120)) WHEN 3 THEN DATEADD(d, -2, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120)) WHEN 4 THEN DATEADD(d, -3, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120)) WHEN 5 THEN DATEADD(d, -4, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120)) WHEN 6 THEN DATEADD(d, -5, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120)) ELSE DATEADD(d, -6, CAST(YEAR(@date) AS CHAR(4)) + '-01-04') END THEN (DATEDIFF(d, CASE (DATEPART(dw, CONVERT(datetime, CAST(YEAR(@date) - 1 AS CHAR(4)) + '-01-04', 120)) + @@DATEFIRST - 1) % 7 WHEN 1 THEN CONVERT(datetime, CAST(YEAR(@date) - 1 AS CHAR(4)) + '-01-04', 120) WHEN 2 THEN DATEADD(d, -1, CONVERT(datetime, CAST(YEAR(@date) - 1 AS CHAR(4)) + '-01-04', 120)) WHEN 3 THEN DATEADD(d, -2, CONVERT(datetime, CAST(YEAR(@date) - 1 AS CHAR(4)) + '-01-04', 120)) WHEN 4 THEN DATEADD(d, -3, CONVERT(datetime, CAST(YEAR(@date) - 1 AS CHAR(4)) + '-01-04', 120)) WHEN 5 THEN DATEADD(d, -4, CONVERT(datetime, CAST(YEAR(@date) - 1 AS CHAR(4)) + '-01-04', 120)) WHEN 6 THEN DATEADD(d, -5, CONVERT(datetime, CAST(YEAR(@date) - 1 AS CHAR(4)) + '-01-04', 120)) ELSE DATEADD(d, -6, CONVERT(datetime, CAST(YEAR(@date) - 1 AS CHAR(4)) + '-01-04', 120)) END, @date) / 7) + 1 -- Exception where @date is part of week 1 of the following year WHEN @date >= CASE (DATEPART(dw, CONVERT(datetime, CAST(YEAR(@date) + 1 AS CHAR(4)) + '-01-04', 120)) + @@DATEFIRST - 1) % 7 WHEN 1 THEN CONVERT(datetime, CAST(YEAR(@date) + 1 AS CHAR(4)) + '-01-04', 120) WHEN 2 THEN DATEADD(d, -1, CONVERT(datetime, CAST(YEAR(@date) + 1 AS CHAR(4)) + '-01-04', 120)) WHEN 3 THEN DATEADD(d, -2, CONVERT(datetime, CAST(YEAR(@date) + 1 AS CHAR(4)) + '-01-04', 120)) WHEN 4 THEN DATEADD(d, -3, CONVERT(datetime, CAST(YEAR(@date) + 1 AS CHAR(4)) + '-01-04', 120)) WHEN 5 THEN DATEADD(d, -4, CONVERT(datetime, CAST(YEAR(@date) + 1 AS CHAR(4)) + '-01-04', 120)) WHEN 6 THEN DATEADD(d, -5, CONVERT(datetime, CAST(YEAR(@date) + 1 AS CHAR(4)) + '-01-04', 120)) ELSE DATEADD(d, -6, CONVERT(datetime, CAST(YEAR(@date) + 1 AS CHAR(4)) + '-01-04', 120)) END THEN 1 -- Calculate the ISO week number for all dates that are not part of the exceptions above ELSE (DATEDIFF(d, CASE (DATEPART(dw, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120)) + @@DATEFIRST - 1) % 7 WHEN 1 THEN CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120) WHEN 2 THEN DATEADD(d, -1, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120)) WHEN 3 THEN DATEADD(d, -2, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120)) WHEN 4 THEN DATEADD(d, -3, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120)) WHEN 5 THEN DATEADD(d, -4, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120)) WHEN 6 THEN DATEADD(d, -5, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120)) ELSE DATEADD(d, -6, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120)) END, @date) / 7) + 1END AS IsoWeek

RE: On the Trail of the ISO Week

Henk Schreij — Wed, 23 Mar 2005 08:51:00 GMT

Cris:If you use the ISO format for dates (yyyymmdd), not the yyyy-mm-dd format, SQL Server is not vulnerable for myd or dmy dates.See CONVERT ISO 112 (or 12 for yymmdd) in BOL.SQL server accepts '20050323' (today, y=2005, m=03, d=23) either in Europe or USA.

RE: On the Trail of the ISO Week

John Valore — Wed, 02 Mar 2005 05:45:00 GMT

Hi,

Just an update.

I was going to use the functionality in an udf environment, but "forgot" that it is not possible to issue SET commands in an udf.

So I had to look at Chris code again when it suddenly strooke me, that I should replace all the '-01-04' with '-04-01'.

Bingo.

John Valore

RE: On the Trail of the ISO Week

John Valore — Wed, 02 Mar 2005 02:34:00 GMT

Thanks for the reply Nic,

I took some time before someone responded, so I had actually abandoned the approach and settled with the standard datepart function.

Thanks to your response, I looked at the problem again and have now solved the problem. I still have to input the date in the format yyyy-mm-dd :

set @startdate =CAST('2005-01-27' as smalldatetime)

but if I include a statement :set dateformat mdy

(default american date input/output), the code from Chris Hedgate now works.

The devil is apparently still in the details and the several datetime formats.

Thank you very much.

John Valore

RE: On the Trail of the ISO Week

Nic Washington — Tue, 01 Mar 2005 02:52:00 GMT

I tried John's code - I had to change the @StartDate to '2005-01-27'.

I had no problems - I received an ISO Week of "4"

Regards,

Nic Washington

RE: On the Trail of the ISO Week

John Valore — Wed, 26 Jan 2005 08:26:00 GMT

I have some problems when I try to implement this solution. I get results in the fourties and up. When I eg. input 2005-01-27 a normal datepart with wk gives the result 5, but this function gives the result 44.

Declare @startdate as smalldatetimeset @startdate ='2005-27-01'SELECT @startdate,DATEPART(wk, @startdate) as Sql_weeknr, CASE-- Exception where @startdate is part of week 52 (or 53) of the previous yearWHEN @startdate < CASE (DATEPART(dw, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04') + @@DATEFIRST - 1) % 7WHEN 1 THEN CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04'WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')ELSE DATEADD(d, -6, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')ENDTHEN(DATEDIFF(d,CASE (DATEPART(dw, CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04') + @@DATEFIRST - 1) % 7WHEN 1 THEN CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04'WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04')WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04')WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04')WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04')WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04')ELSE DATEADD(d, -6, CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04')END,@startdate) / 7) + 1-- Exception where @startdate is part of week 1 of the following yearWHEN @startdate >= CASE (DATEPART(dw, CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04') + @@DATEFIRST - 1) % 7WHEN 1 THEN CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04'WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04')WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04')WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04')WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04')WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04')ELSE DATEADD(d, -6, CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04')ENDTHEN 1ELSE -- Calculate the ISO week number for all dates that are not part of the exceptions above(DATEDIFF(d,CASE (DATEPART(dw, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04') + @@DATEFIRST - 1) % 7WHEN 1 THEN CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04'WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')ELSE DATEADD(d, -6, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')END,@startdate) / 7) + 1END AS IsoWeek

A bit puzled about this

Best regards

John Valore

On the Trail of the ISO Week

Chris Hedgate — Wed, 12 Jan 2005 13:44:00 GMT

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