jafwatt jafwatt - 5 months ago 31
SQL Question

Why are SQL Server week numbers different to Java week numbers?

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

return a different week number to

DATEPART(WEEK, '2012-01-01')

Is there an approach to resolving this difference or will I have to choose to use SQL Server or Java week numbers?



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: