Weekly reports are usually requested by customers who want to follow their activity results by weeks running from Monday to Sunday. The most common way to collect weekly data is by grouping date ranges by their Week No. of the Year.
As you will see in this post, when I started investigating this topic I found some interesting information about the week format in both Oracle Database and SQL Server, which I hope will be useful to others using these tools.
Oracle Database
Throughout my many years working with Oracle I assumed that the ‘ww’ mask returns the Week No. of Year according to the standard week (running from Monday to Sunday). After doing some queries I was surprised to discover that days belonging to the same week can actually have a different Week No depending on the day of the week that the year started.
For example, Week 1 of 2010 started on a Friday, therefore every Week No in 2010 will run from Friday to Thursday:
After some research I found the following documentation for Oracle DB that provides an additional explanation on this subject:
http://download.oracle.com/docs/cd/B10500_01/server.920/a96529/ch7.htm#5186
By applying this new knowledge to the previous query I was able to compare the two methods:
SQL Server
For those of you who are SQL Server programmers, I have also done some investigation on this subject. SQL Server 2008 supports ISO week as an argument of its DATEPART function. Prior versions use the regular ‘ww’ or ‘wk’ mask based on January 1st. The first day of a week is defined in the DATEFIRST session setting which by default sets Sunday to be the first day of the week.
You can use this user defined function for calculating the ISO week number in prior versions of SQL Server 2008 as follows:
After implementing the function above, you can run the following query to compare the common week mask and ISO week:
Notice that Friday is numbered as the 6th day of the week. The first day of a week depends on the DATEFIRST session parameter, which by default is set to 7 (weeks start on Sunday and end on Saturday).
- To see the current setting of DATEFIRST, use the @@DATEFIRST function.
- The setting of SET DATEFIRST is set at execute or run time and not at parse time.
- To set Monday as the first day of the week execute:
To finish off, I would like to list some advantages and disadvantages about using ISO week numbering:
Advantages:
- All weeks have an integral number of days (i.e. there are no partial weeks).
- All years have an integral number of weeks.
- The date directly tells the weekday.
- All weeks of the year start on a Monday and end on a Sunday.
- When used by itself without using the concept of month, all the weeks in a year are the same except that some years have week 53 at the end.
- The weeks are the same as used with the Gregorian calendar. Dates represented as yyyy-Www-d or yyyyWwwd can be sorted as strings.
Disadvantages:
- Not all parts of the world have a work week that begins with Monday. For example, in some countries, the work week may begin on Saturday or Sunday.
- In the link below you can find an extended list of countries and their week numbering rules: http://www.pjh2.de/datetime/weeknumber/wnc.php?l=en
- Many organizations use their own calendars which do not follow ISO standard (i.e. Fiscal calendars or academic calendars).
In summary, things are not always as they seem. As my professors always told me, in the business of computer science we can never afford to make assumptions and should always read the documentation and check sources.
I would be interested to know if anyone else has additional information or comments to share on this topic. Please feel free to leave a comment below.