Calculation problem of the Oracle week

advertisements

I am using Oracle's to_char() function to convert a date to a week number (1-53):

select  pat_id,
    pat_enc_csn_id,
    contact_date,
    to_char(contact_date,'ww') week,
    ...

the 'ww' switch gives me these values for dates in January of this year:

Date        Week
1-Jan-10    1
2-Jan-10    1
3-Jan-10    1
4-Jan-10    1
5-Jan-10    1
6-Jan-10    1
7-Jan-10    1
8-Jan-10    2
9-Jan-10    2
10-Jan-10   2
11-Jan-10   2
12-Jan-10   2

a quick look at the calendar indicates that these values should be:

Date         Week
1-Jan-10    1
2-Jan-10    1
3-Jan-10    2
4-Jan-10    2
5-Jan-10    2
6-Jan-10    2
7-Jan-10    2
8-Jan-10    2
9-Jan-10    2
10-Jan-10    3
11-Jan-10    3
12-Jan-10    3

if I use the 'iw' switch instead of 'ww', the outcome is less desirable:

Date         Week
1-Jan-10    53
2-Jan-10    53
3-Jan-10    53
4-Jan-10    1
5-Jan-10    1
6-Jan-10    1
7-Jan-10    1
8-Jan-10    1
9-Jan-10    1
10-Jan-10   1
11-Jan-10   2
12-Jan-10   2

Is there another Oracle function that will calculate weeks as I would expect or do I need to write my own?

EDIT

I'm trying to match the logic used by Crystal Reports. Each full week starts on a Sunday; the first week of the year starts on whichever day is represented by January 1st (e.g. in 2010, January 1st is a Friday).


When using IW, Oracle follows the ISO 8601 standard regarding week numbers (see http://en.wikipedia.org/wiki/ISO_8601). That is the same standard than the one we generally use in Europe here.

Your problem is also mentioned on the Oracle forum: http://forums.oracle.com/forums/thread.jspa?threadID=947291 and http://forums.oracle.com/forums/message.jspa?messageID=3318715#3318715. Maybe you can find a solution there.