On a recent project for a customer, there was a special need for using week numbers instead of dates in their application. For those of you not familiar with the concept of week numbers, it is simply assigning a running number to every week of the year. Generally the first week of the year is week 1, and so fort. There is a few different approaches on which week is actually the first of the year, in Denmark where the project was made we use the ISO8601 standard. It means that the first week of the year is the first week with a Thurdays (or simply four days, as our weeks start on Monday).
Well enough background, there was two needs in the project.
Showing dates as weeks
This one was fairly simple. The customer uses Microsoft SQL Server R2, it has a nice builtin feature that can simple convert the date to week numbers by calling:
SELECT DATEPART(ISOWK, GETDATE())
Converting a week number and a year to the first day of the week
This was a little more tricky. The user wanted to be able to input their dates in the format of week number and year, and since the system needs to work on dates a conversion was necessary. After a little digging I found that the following formula was able to do the trick
SET DATEFIRST 1
declare @Week int set @Week = 49
declare @Year int set @Year = 2011
select dateadd (week, @Week, dateadd (year, @Year-1900, 0)) - 4 - datepart(dw, dateadd (week, @Week, dateadd (year, @year-1900, 0)) - 4) + 1
The important thing to note here is that the SET DATEFIRST 1 is important, because the first day of our week is Monday and not Sunday (7). This -4 is because of the there has to be at least four days in the first week of the year. The last +1 is to make the first day of the week Monday and not Sunday. And yes it might seem like some mess, but unfortunately the DATEADD operation don’t allow the use of ISOWK, at least not yet, so this is how it has to be for now.