Home > Articles > Data

  • Print
  • + Share This
From the author of

Week of the Year

The Big Eight DBMSs have five different ways to calculate a week number:

  1. The first day of the first week is January 1. This has the advantage of avoiding weeks that are split across years, but it doesn't correspond to the "first day of the week" possibilities that we've just discussed. Supporters include Oracle.

  2. Weeks start on Monday, and the first week of the year is the first week that has a Thursday in it. This has the advantage that all weeks have seven days. On the other hand, this can mean that days between January 1 and January 3 are counted as being part of a week in the previous year. Supporters include ISO, DB2, Ingres, and Oracle.

  3. Weeks start on Sunday, and the first week starts wherever the first Sunday is. This has the same advantages and disadvantages as the previous method.

  4. Weeks start on Monday, and the first week starts wherever the first Monday is. Any days before the first Monday are Week 0. This has nearly the same advantages and disadvantages as methods two and three. Supporters include Ingres.

  5. Weeks start on Sunday, and the first week (Week 1) is the Sunday that begins the week of January 1. Values are from 1 to 54. Supporters include DB2.

Option 2 is what the ISO 8601 standard requires. DB2, Ingres, and Oracle each have two "week" functions: one for a proprietary calculation, and one for the option 2 calculation. ODBC requires a WEEK function but is vague about the requirements.

  • + Share This
  • 🔖 Save To Your Account