Oracle week calculation issue

Question

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

"a quick look at the calendar indicates that these values should be" Which calendar? Microsoft Outlook? — APC, Feb 18 '10 at 06:22

score 2 · Answer 1 · answered Feb 17 '10 at 22:26

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.

score 1 · Answer 2 · answered Feb 25 '13 at 20:32

1

I know this is old, but still a common question.

This should give you the correct results in the smallest amount of effort:

select  pat_id, 
pat_enc_csn_id, 
contact_date, 
to_char(contact_date + 1,'IW') week,
...

answered Feb 25 '13 at 20:32

akoman

11
1

score 0 · Answer 3 · answered Feb 17 '10 at 19:54

0

Since it looks like you are using your own special definition of the week number you'll need to write your own function.

It might be helpful that NLS_TERRITORY affects the day with which a week starts as used by the D Format Model

see also:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements004.htm#SQLRF00210

and

http://www.adp-gmbh.ch/ora/sql/to_char.html

answered Feb 17 '10 at 19:54

Jens Schauder

77,657
34
181
348

The fun part is that the Oracle WW-format week number has, in 2017, a 1-day week number 53, while US/ISO weeks always have 7 days. In the US, the week starts on Sunday, against Genesis calling that the 7th day :-) – Roland Apr 10 '18 at 10:36
@Roland I'd expect Genesis to mean Sabbath when referring to the 7th day. – Jens Schauder Apr 10 '18 at 11:28
Okaaaayyyyy,,,,, now https://en.wikipedia.org/wiki/Sabbath#Secular_traditions gives a lot of info, too much to understand at one reading. Sabbath may be on Friday, or Saturday, or Sunday, depending on your holy book. It remains a mystery what is the logic of a calendar with the first day of the work week on Sunday. Is the US jewish-oriented and Europe christian-oriented? Are US workers more often free on Sunday or on Saturday? – Roland Apr 10 '18 at 15:32

score 0 · Accepted Answer · edited May 23 '17 at 12:31

Based on this question, How do I calculate the week number given a date?, I wrote the following Oracle logic:

CASE
  --if [date field]'s day-of-week (e.g. Monday) is earlier than 1/1/YYYY's day-of-week
  WHEN to_char(to_date('01/01/' || to_char([date field],'YYYY'),'mm/dd/yyyy'), 'D') - to_char([date field], 'D') > 1 THEN
    --adjust the week
    trunc(to_char([date field], 'DDD') / 7) + 1 + 1 --'+ 1 + 1' used for clarity
  ELSE trunc(to_char([date field], 'DDD') / 7) + 1
END calendar_week

Oracle week calculation issue

4 Answers4

Linked