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

Question

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);
cal.get(Calendar.WEEK_OF_YEAR);

and

org.joda.time.DateTime date = new org.joda.time.DateTime(2012, 01, 01, 0, 0);
date.getWeekOfWeekyear();

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?

TIA

Answer 1

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:

In SQL Server the DATEPART(WEEK, ..) function is much more simplistic, it simply calculates the number of week boundaries (as defined by DATEFIRST) between the first of january, and the input date, so the 1st January will always be week 1. You may wish to consider using:

SELECT DATEPART(ISO_WEEK, '01/01/2012')

This gets the week number as defined by 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).

Since, as stated SQL Server is more simplistic in it's calculations, this can't be configured, which means that you will need to configure your week numbers within Java. Simply ensure you set up your calendar in Java with the correct values for getFirstDayOfWeek() and getMinimalDaysInFirstWeek():

public static Calendar getISOCalendar() {
    Calendar calendar = Calendar.getInstance();
    calendar.setMinimalDaysInFirstWeek(4);
    calendar.setFirstDayOfWeek(Calendar.MONDAY);
    return calendar;
}

Then you can esnsure consistent week numbers.

public static void main(String args[]) {
    Calendar calendar = getISOCalendar();
    calendar.set(2012,0,1);
    System.out.println(calendar.get(Calendar.WEEK_OF_YEAR));
}

------
52

Answer 2

According to their respective docs, java.util.calendar, joda, and DATEPART all return an integer between 1 and 53 indicating the week. This value is based on what is defined as the first day of the week and the default calendar (e.g. Gregorian, Chinese). I would check your defaults or see what each thinks the first day of the week is.

Answer 3

tl;dr

int iso8601WeekNumber = 
    ZonedDateTime.now( ZoneId.of ( "America/Montreal" ) )
        .get( IsoFields.WEEK_OF_WEEK_BASED_YEAR )

Many definitions of a week

There are many definitions of a week.

For some people the first day of a week is a Sunday, others Mondays, and still others some other day.

For some people the first week of the year contains January 1st, while some other folks count the first week as the one containing that start-of-week day mentioned above, while others count week one as containing a specific day-of-week.

So you should never assume the meaning of a "week" or "week number" without studying the documentation.

Avoid legacy date-time classes

In general, you should avoid the old legacy date-time classes bundled with the earliest versions of Java as they are poorly-designed, confusing, and troublesome.

Here specifically, the java.util.Calendar class has a definition of week that varies by locale. So do not use this if you want reliable constant results.

ISO 8601

The ISO 8601 standard defines formats and related issues for date-time values. It specifically defines the meaning of a week and week-of-year:

• Week runs from Monday-Sunday, numbered 1-7 where Monday = 1.
• Week # 1 contains the first Thursday of the year, yielding 52 or 53 weeks per year.

Joda-Time

The Joda-Time library uses the ISO 8601 standard definition of week and week-of-year.

However, be aware that the Joda-Time project is now in maintenance mode, with the team advising migration to java.time.

java.time

The java.time classes use the ISO 8601 definition of a week: Week # 1 has first Thursday, with week running Monday-Sunday.

The IsoFields class defines a week-based year. We can ask for the:

• Week-of-year number (WEEK_OF_WEEK_BASED_YEAR)
• Year number of the week-based year (WEEK_BASED_YEAR).

First we get the current date-time.

ZoneId zoneId = ZoneId.of ( "America/Montreal" );
ZonedDateTime now = ZonedDateTime.now ( zoneId );

Interrogate that date-time object, asking about the standard week-based year.

int week = now.get ( IsoFields.WEEK_OF_WEEK_BASED_YEAR );
int weekYear = now.get ( IsoFields.WEEK_BASED_YEAR );

Dump to console.

System.out.println ( "now: " + now + " is week: " + week + " of weekYear: " + weekYear );

now: 2016-01-17T20:55:27.263-05:00[America/Montreal] is week: 2 of weekYear: 2016

For more info, see this similar Question: How to calculate Date from ISO8601 week number in Java.

WeekFields

In java.time you can also call upon the WeekFields class, such as WeekFields.ISO.weekBasedYear(). Should have the same effect as IsoFields in later versions of Java 8 or later (some bugs were fixed in earlier versions of Java 8).

---

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, .Calendar, & java.text.SimpleDateFormat.

The Joda-Time project, now in maintenance mode, advises migration to java.time.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

Where to obtain the java.time classes?

• Java SE 8 and SE 9 and later
  • Built-in.
  • Part of the standard Java API with a bundled implementation.
  • Java 9 adds some minor features and fixes.
• Java SE 6 and SE 7
  • Much of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
• Android
  • The ThreeTenABP project adapts ThreeTen-Backport (mentioned above) for Android specifically.
  • See How to use….

The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.

Answer 4

I think it might have something to do with 2011 and the idea that it has 53 weeks according to SQLServer. Looking at Jan / New Year SQL problems!, this solution might help to handle the SQLServer stuff better:

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

Answer 5

   System.out.println("get date range from week number and year in java");
   System.out.println(); // print a blank line

   // get the input from the user
   Scanner sc = new Scanner(System.in);

   System.out.print("Enter the week : ");
   int weekNumber  = sc.nextInt(); 
   System.out.print("Enter the Year: ");
   int year = sc.nextInt() ;



   Calendar cal = Calendar.getInstance();
   //cal.setTime(new Date());

   cal.set(Calendar.YEAR, year);
   cal.set(Calendar.WEEK_OF_YEAR, weekNumber);

   SimpleDateFormat formatter = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");

   cal.set(Calendar.DAY_OF_WEEK, Calendar.MONDAY);
   System.out.println(formatter.format(cal.getTime())); // start date

   cal.add(Calendar.DAY_OF_WEEK, 6);
   System.out.println(formatter.format(cal.getTime())); // end date