I'm working on a reporting system using Java 6 and SQL Server 2008. For some queries I want to look at the data by week number. I'm using Java to fill in gaps in the the data to make a continuous time line and I have found that
java.util.Calendar cal = new java.util.GregorianCalendar();
cal.set(2012, 0, 1);
org.joda.time.DateTime date = new org.joda.time.DateTime(2012, 01, 01, 0, 0);
Java is more sophisticated when it comes to calculating week numbers, whereas SQL-Server DATEPART(WEEK... is more simplistic. I found the following documented here
"When setting or getting the WEEK_OF_MONTH or WEEK_OF_YEAR fields, Calendar must determine the first week of the month or year as a reference point. The first week of a month or year is defined as the earliest seven day period beginning on getFirstDayOfWeek() and containing at least getMinimalDaysInFirstWeek() "
I think this defaults to the ISO standards which is the first week of the year with a thursday in (Monday-Sunday weeks where at least 4 days are the year). Consider using:
SELECT DATEPART(ISO_WEEK, '01/01/2012')