dotnet clr: Hi,
I was looking for a CLR and T-SQL solution to calculate ISO8601 week code
when I ran into a post your team has made here:
http://blogs.msdn.com/sqlclr/archive/2005/11/11/491981.aspx

Since we are still using SQL2000 and have not yet made the immediate
decision to switch to SQL2005, I decided to compare the results of the code
as presented on that posting with results from SQL2000 Books Online (Create
User Defined Function Sample A), a second T-SQL posting from
http://www.sqlservercentral.com/columnists/chedgate/onthetrailoftheisoweek.asp,
and VB6 (with the help of http://support.microsoft.com/kb/q200299/). I
stored all the results from each trial run on separate tables and then
compared the outcome where the input dates are the same but the week numbers
are different between any two methods. I was surprised to see that the CLR
version returned different values from the VB6 version even with the fix
mentioned from the KB. I was even more surprised to see that the 2 T-SQL
solutions returned the same values as the VB6 solution. The date range
covered 10k days starting from Jan 1, 2005. Is this a bug in the CLR's
DatePart function?

The input dates which resulted in discrepeancies with the CLR result are as
follows:

InputDate CLR TSQL1 TSQL2 VB6
12/31/2007 53 1 1 1
12/29/2008 53 1 1 1
12/30/2008 53 1 1 1
12/31/2008 53 1 1 1
12/31/2012 53 1 1 1
12/30/2013 53 1 1 1
12/31/2013 53 1 1 1
12/29/2014 53 1 1 1
12/30/2014 53 1 1 1
12/31/2014 53 1 1 1
12/31/2018 53 1 1 1
12/30/2019 53 1 1 1
12/31/2019 53 1 1 1
12/30/2024 53 1 1 1
12/31/2024 53 1 1 1
12/29/2025 53 1 1 1
12/30/2025 53 1 1 1
12/31/2025 53 1 1 1
12/31/2029 53 1 1 1
12/30/2030 53 1 1 1
12/31/2030 53 1 1 1
12/29/2031 53 1 1 1
12/30/2031 53 1 1 1
12/31/2031 53 1 1 1

Regards,
Tristan

Hello Tristan,

Welcome to the MSDN newsgroup.

From your description, I understand you're encountering some inconsistent
behavior between the some custom ISO 8601 weeknum calculate code and the
..net framework's weekofYear calucation, you found that for some end days of
a year, the .net framework's calculation will return "53"(or the last
week's number) while the other calculation code will return "1"(the first
weeknum), correct?

I've performed some tests through T-SQL and the .net framework
functions(use the following code) and the result did conform to your
finding.

=======================
protected void Page_Load(object sender, EventArgs e)
{
DateTime date = DateTime.Parse("1977-01-02 ");



System.Globalization.Calendar cal =
CultureInfo.InvariantCulture.Calendar;

int weekNo = cal.GetWeekOfYear(date,
CalendarWeekRule.FirstFourDayWeek,
DayOfWeek.Monday );

DayOfWeek dayNo = cal.GetDayOfWeek(date);

Response.Write("<br/>WeekOfYear: " + weekNo);
Response.Write("<br/>WeekOfYear: " + dayNo);

}
=================================

After some further review on the ISO 8601 datetime standard, here are some
of my understanding on this problem:

In the ISO 8601 standard, it define the week number in a year as below:

================
Mutually equivalent definitions for week 01 are:

the week with the year's first Thursday in it
the week with 4 January in it
the first week with the majority (four or more) of its days in the starting
year
the week starting with the Monday in the period 29 December - 4 January
If 1 January is on a Monday, Tuesday, Wednesday or Thursday, it is in week
01. If 1 January is on a Friday, Saturday or Sunday, it is in week 52 or 53
of the previous year.
==================

Also, the total week index of a year can range from 1 to 52 or 53. Also ISO
8601 only define which start days of a year should be in the frist week of
that year(W01), but not the end days in that year. That means for some end
days in a year, such as 12/31/2007, it is in the first week(W01) of year
2008 , but since the week number of year 2007 can range from 1--52, or
1--53, we can also consdier 12/31/2007 as the 53th week of year 2007.
Therefore, the below expression are both correct:

2007-W53-1

2008-W01-1

ISO 8601 hasn't made forced statement on this. And the .net framework's
implementation choose to use the (2007-W53-1), I think it is because for a
certain day, it is more natural to consider its week number as the value
among its own year's weeknumber range(rather than its next year's
weeknumber).

And as for the T-SQL or other .net custom code on calculating ISO 8601 week
number, they all use their own implementation for such edging cases(the
last days in a year), there is no standards to say which one is correct or
wrong.

This is my understanding on this problem. Hope this helps. If you have
anything unclear or still have any other questions on this, please feel
free to post here.

BTW, here are some web articles describing the ISO 8601 standards:

#ISO 8601
http://en.wikipedia.org/wiki/ISO_8601#Week_dates

#A summary of the international standard date and time notation
http://www.cl.cam.ac.uk/~mgk25/iso-time.html


Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead



==================================================

Get notification to my posts through email? Please refer to

http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.



Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial

response from the community or a Microsoft Support Engineer within 1
business day is

acceptable. Please note that each follow up response may take approximately
2 business days

as the support professional working with you may need further investigation
to reach the

most efficient resolution. The offering is not appropriate for situations
that require

urgent, real-time or phone-based interactions or complex project analysis
and dump analysis

issues. Issues of this nature are best handled working with a dedicated
Microsoft Support

Engineer by contacting Microsoft Customer Support Services (CSS) at

http://msdn.microsoft.com/subscriptions/support/default.aspx.

==================================================



This posting is provided "AS IS" with no warranties, and confers no rights.

[quoted text, click to view]
(snip)

Hi Tristan,

If you look at the description of the ISO week in Wikipedia (link
below), you'll see that for all dates that show week 53 as calculated by
CLR and week 1 for TSQL1, TSQL2, and VB6, 1 is in fact the correct
answer. The algorithm used in the CLR post is wrong.

Wikipedia: http://en.wikipedia.org/wiki/ISO_8601#Week_dates

Note: I didn't read all the links you supplied, I just checked to
confirm that the bug is indeed in the CLR function.

--

Hi Triastan,

Have you got any further idea on this issue? I've sent this behavior to our
internal team for discussion and if there is any further result I get, I'll
update you as soon as possible.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.

Hello Triastan,

After some further discussion with our globalization engineers, they've
confirmed that the .net framework Calendar.GetWeekOfDay method is not
naturally designed to match the ISO 8601 standards. So the information on
the SQL CLR team blog

http://blogs.msdn.com/sqlclr/archive/2005/11/11/491981.aspx

should means:

..net framework's built-in method doesn't directly support ISO 8601 standard
weeknum calculation, however, it is easy to to use existing .net classes to
generate a simple function for reutrning ISO 8601 weeknum, e.g:

=========================================
public int GetIso8601WeekOfYear(DateTime time)
{
System.Globalization.Calendar cal =
CultureInfo.InvariantCulture.Calendar;

DayOfWeek day = cal.GetDayOfWeek(time);
if (day >= DayOfWeek.Monday && day <= DayOfWeek.Wednesday)
{
time = time.AddDays(3);
}

return cal.GetWeekOfYear(time, CalendarWeekRule.FirstFourDayWeek,
DayOfWeek.Monday);
}
============================

And here are some good articles (from our globalization technical lead's
blog) which have detailed description on this topic:

http://blogs.msdn.com/michkap/archive/2005/11/14/492249.aspx

http://blogs.msdn.com/michkap/archive/2006/01/26/517734.aspx

Hope this helps. If there is any other information you need, please feel
free to post here.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead



This posting is provided "AS IS" with no warranties, and confers no rights.

Hello Triastan,

How are you doing on this issue, doest the further information in my last
reply helps you a little? If there is any other information you wonder on
this, please feel free to post here.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.

Hi Steven,
Thanks for clarifying the datepart internals for me. I looked at
your code with the
workaround and will be rewriting it in vb.net and then eventually add the
assembly to SQL
as a UDF. I do not know offhand whether the namespace you referenced is
legal within
SQLCLR but it should not take too long to find out.

Regards,
Tristan

Thanks for your followup Tristan,

As for the namespace, do you mean the "System.Globalization"? This is a
standard namespace in .net framework base class library and SQL CLR for SQL
Server support all the base class library classes. However, you may need to
adjust the CLR trust level(security permission) when you want to use some
restricted classes(which will access protected resources), but I think the
System.Globalization classes are not in those restricted classes).

Anyway, if you meet any further problem on this, please feel free to post
here.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead

This posting is provided "AS IS" with no warranties, and confers no rights.

Hello,

just an extended version implementing the year out argument ...

[code]
/// <summary>
/// Returns an ISO8601 week number
/// <para>This presumes that weeks start with Monday. Week 1 is the 1st week of the year with a Thursday in it.
/// </para>References :
/// <para>"ISO 8601 Week of Year format in Microsoft .Net" by Shawn Steele (http://blogs.msdn.com/shawnste/archive/2006/01/24/iso-8601-week-of-year-format-in-microsoft-net.aspx)
/// </para>"DatePart result discrepancies when calculating ISO8601 week" by Steven Cheng (http://www.eggheadcafe.com/conversationposter.aspx?messageid=27602745&groupid=435)
/// </summary>
/// <param name="date">[in] Date whose the week number is required</param>
/// <param name="year">[out] Year of the returned week number</param>
/// <returns>Week number of the specified date</returns>
public static int GetIso8601WeekOfYear(DateTime date, out int year)
{
// Need a calendar. Culture's irrelevent since we specify start day of week
Calendar calendar = CultureInfo.InvariantCulture.Calendar;

// Seriously cheat. If its Monday, Tuesday or Wednesday, then it'll
// be the same week# as whatever Thursday, Friday or Saturday are,
// and we always get those right
DayOfWeek day = calendar.GetDayOfWeek(date);
if (day >= DayOfWeek.Monday && day <= DayOfWeek.Wednesday)
{
date = date.AddDays(3);
}

year = date.Year;

// Return the week of our adjusted day
int weekNumber = calendar.GetWeekOfYear(date, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday);

if((weekNumber >= 52) && (date.Month < 12)) { year--; }

return weekNumber;
}
[/code]

EggHeadCafe.com - .NET Developer Portal of Choice