December 09, 2004

Finding week number of the year

Recently, I had a requirement to generate a dynamic graph based on weekly data. For this, I decided to use the "group by" clause to group the data by weeks. Very quickly, I realized I was dealing with a nasty problem. The same set of data, but from different databases, will return different results. Determining a consistent week of the year among the different databases was not easy. Why? Because, each database generates the week number of a year differently.

For example, assume 10 units were sold on 12/30/2003 and 20 units on 1/1/2004. After using group by, the result would be 10 units for week number 53 and 20 units for week number 1 in SQLserver. However, in Oracle it would be 10 units for week number 52 (not 53) and 20 units for week number 1. This difference in week number will appear throughout the year and not just at the beginning or end of the year.

In the following tables, I am listing some sample dates and the week number of each date.

Using datepart(param, column) in SQLServer, I get the following results. Notice the effect of the SET DATEFIRST statement. 7 is the default value for SQLServer.

datefirst =1datefirst =1datefirst = 7datefirst = 7
12/27/20035212/27/200352
12/28/20035212/28/200353
12/29/20035312/29/200353
12/30/20035312/30/200353
12/31/20035312/31/200353
1/1/200411/1/20041
1/2/200411/2/20041
1/3/200411/3/20041
1/4/200411/4/20042
1/5/200421/5/20042
1/6/200421/6/20042
1/7/200421/7/20042
1/8/200421/8/20042
1/9/200421/9/20042

Using to_char(column, param) in Oracle, I get the following results for the same dates.

to_char(ww)to_char(ww)to_char(iw)to_char(iw)
12/27/20035212/27/200352
12/28/20035212/28/200352
12/29/20035212/29/20031
12/30/20035212/30/20031
12/31/20035312/31/20031
1/1/200411/1/20041
1/2/200411/2/20041
1/3/200411/3/20041
1/4/200411/4/20041
1/5/200411/5/20042
1/6/200411/6/20042
1/7/200411/7/20042
1/8/200421/8/20042
1/9/200421/9/20042

As you can see from the above data, for a given date, the week number will vary in each database. How does it matter to me? In my case, the data set could potentially be millions of rows. I am using a single query to get the grouped data. However, in the graph, I would like to display all weeks in the x-axis even if it has null data for a particular week. That is, if week 20 has no data, I would like the y-axis value to be 0. Therefore, I programmatically generated all the week numbers given a start and end date. I did this using boost datetime library. Boost uses ISO 8601 standard for generating the week number. To match these generated week numbers, I had to find a way to get ISO standard week numbers in each database. Oracle already had a way to do that. SQLServer did not have a ready made solution. But, I found a ISOWeek function from MSDN, which did what I wanted.

To get a consistent week number, I used the following.
SQLServer:
Set Datefirst 1
ISOWeek(date column)

Oracle:
to_char(date column, "IW")

Boost:
week_iterator
week_number()

Using the above methods, I could generate the same week number no matter which database is used.

DateWeek number
12/27/200352
12/28/200352
12/29/20031
12/30/20031
12/31/20031
1/1/20041
1/2/20041
1/3/20041
1/4/20041
1/5/20042
1/6/20042
1/7/20042
1/8/20042
1/9/20042

Note: In most cases, this might not be a problem. Because, the system would involve just one database or the users may not want ISO standard week number.

1 Comments:

Anonymous Dirk said...

I use weeknumbers on occasion and recently found www.clndr.org. Good for a quick and simple reference.

October 11, 2008 at 11:56 AM  

Post a Comment

<< Home