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?
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.