Getting a WeekNumber from a Date in SSRS 2005 and 2008

Many reports have to deal with week numbers. Often you'll have a DateTime to work with, either from a dataset or a parameter.

So, how do we convert this lovely little DateTime to a WeekNumber? Search for that online and you'll get some solutions suggesting you change your query to give you a weeknumber, some solutions will do some fancy (and mostly erroneous) math to calculate the WeekNumber and yet others suggest you just don't do that.

Getting the WeekNumber from your DataSet really is what you want, but then again you don't really want your DataSource to calculate the WeekNumber in all instances. The reason for that is related to the reason why DateTime does not have a WeekNumber:

What, really, is a weeknumber? Why, silly, it's the number of the week the day is a part of.

That's true, but how are the weeks numbered? Do they start with week 0 or week 1? If the week numbering starts at week 1 the last week should be week 53, or should it be week 52 (a week with more than 7 days)? How about if the year starts on a non-monday, what week are the first days in (they could be in the last week of the previous year, in the first (0th or 1st) of the new year, or perhaps even week-less).

How about weeks, do they start on Mondays, Sundays, Saturdays, Fridays or some other day? That's kind of important when trying to calculate the first week of the year and counting the weeks until a date.

Calendaring issues get complicated very fast, and the fun thing about them is that there are so many ways people keep track of their measured days. The year could start at wildly differing dates, it's silly to assume that the first of January is the first day of a year, and if it's not you'd better not be DateDiffing that modulus 7 to get a WeekNumber.

I'll just happily ignore systems with weeks of more or less than 7 days. That would get hard.

With all this in mind also consider that your datasource may not be using the same calendaring as your reporting system. If your report may be run in Tehran and your DataSource is a database of some sort in Tel-Aviv they may not be using the same calendar, but it'd be an admirable instance of Persian/Semittic cooperation.

To get your week number you really should defer all the international issues to the people who've researched it and been paid to do a good job of it. Luckilly the .Net framework is produced by just such people, and I just love it when I can let it do the work for me.

The .Net framework (in which you report is running) knows about the locale it's running in, and this information can be used to derive the WeekNumber without ever adding together two days.

You just add this code snippet to your Code tab in your report properties:

Function getWeekNumber(ByVal d As DateTime) As Integer
    dim culture as System.Globalization.CultureInfo
    culture = system.Globalization.CultureInfo.CurrentCulture
 
    dim weekRule as System.Globalization.CalendarWeekRule
    weekRule = culture.DateTimeFormat.CalendarWeekRule
 
    dim firstDayOfWeek as System.DayOfWeek
    firstDayOfWeek = culture.DateTimeFormat.FirstDayOfWeek
 
    return culture.Calendar.GetWeekOfYear(d, weekRule, firstDayOfWeek)
End Function

Explanation:
First we get the current culture the report is running under (this would be the culture of the locale of the server running SSRS in our case). From this culture you extract the rule for starting weeks relative to the year and which day of the week the weeks start on. These two values, together with the date you have are passed into the Calendar of the current culture to get the WeekNumber of the date in that context.

It really is harder than it should be, but with this code (which you can just copy/paste) you make a new Calculated Field on your dataset with the following expression (I assume the Date field you want to convert to a WeekNumber is called Date in this instance):

=Code.getWeekNumber(Fields!Date.Value)

And, as they say, Robert is your mother's brother.

Addendum:

If you don't want to go through adding the WeekNumber to your DataSet you could of course just use the above expression straight in your textboxen or the like.

If you don't want the code you can inline it all as such (this is UGLY, don't do it!):

=System.globalization.CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(Fields!Date.Value, System.globalization.CultureInfo.CurrentCulture.DateTimeFormat.CalendarWeekRule, System.globalization.CultureInfo.CurrentCulture.DateTimeFormat.FirstDayOfWeek)