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


On the Trail of the ISO Week


On the Trail of the ISO Week

Author
Message
Michael Valentine Jones
Michael Valentine Jones
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14687 Visits: 11848
Jeff Moden (3/22/2010)
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.


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
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60510
R.P.Rozema
R.P.Rozema
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2896 Visits: 1717
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:

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 isoweeknumber
from (
-- 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
) x
order by 1;





Posting Data Etiquette - Jeff Moden
Posting Performance Based Questions - Gail Shaw
Hidden RBAR - Jeff Moden
Cross Tabs and Pivots - Jeff Moden
Catch-all queries - Gail Shaw


If you don't have time to do it right, when will you have time to do it over?
geoff5
geoff5
Say Hey Kid
Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)Say Hey Kid (674 reputation)

Group: General Forum Members
Points: 674 Visits: 543
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 TargetDate equal the date for which we want to know the ISO week value.

2. Let CurrentThursday 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 FirstThursday 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 always in the first ISO week of the year, which is not true for January 1st.)

4. Let ISO_Week_Value 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 @TargetDate 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 @ThursdayOffset variable is used to find Thursday no matter what the @@datefirst value is.


declare @DateOffset as smallint, @ThursdayOffset as smallint, @TargetDate as datetime,
@CurrentThursday as datetime, @FirstThursday as datetime, @ISO_WeekNumber as tinyint

set @DateOffset = case when @@datefirst > 4 then @@datefirst - 8
when @@datefirst > 1 then @@datefirst - 1 else 0 end
set @ThursdayOffset = case when @@datefirst < 5 then 5 - @@datefirst
else 12 - @@datefirst end

set @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) + 1

select
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))


Michael Valentine Jones
Michael Valentine Jones
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14687 Visits: 11848
This one is fairly short and I did a lot of testing to verify it works OK:
ISO Week of Year Function
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60510

This has the week and day of week included:
ISO Year Week Day of Week Function
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60515Returns 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:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519
ISO_YEAR_WEEK_NO - ISO 8601 year and week in format YYYYWW
Example = 200403

ISO_WEEK_NO - ISO 8601 week of year in format WW
Example = 52

ISO_DAY_OF_WEEK - ISO 8601 Day of week number, Mon=1, Tue=2, Wed=3, Thu=4, Fri=5, Sat=6, Sun=7

ISO_YEAR_WEEK_NAME - ISO 8601 year and week in format YYYY-WNN
Example = 2004-W52

ISO_YEAR_WEEK_DAY_OF_WEEK_NAME - ISO 8601 year, week, and day of week in format YYYY-WNN-D
Example = 2004-W52-2



Edit: Didn't see that this is an old thread and that I had already posted on it. Doze
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