'Week of Year' function/behavior

Question/Problem:

Rather than the week starting on the day that you setup in the preferences (or even just on a Monday) it actually uses the first day of the year as its weekly starting point.

 

So for 2010, every "week" starts on a Friday. This is documented in Defect 171746 - S3-Issue

---

Answer/Solution:

Steps to Reproduce:
1. Navigate to Setup > Company > General Preferences >
First Day of Week = Sunday
Save
2. Lists > Search > Saved Searches > Weekly Supply Sales >$300
Results tab > Columns subtab > Date Created: Function = Week of Year

Actual Result:
1. Transactions created on 10/28/09 are assigned to week 2009-43 and transactions created on 10/29/09 and 10/30/09 are assigned to week 2009-44

Expected Result:
1. Transactions created on 10/28/09 to 10/30/09 should be assigned to the same week

Alternate Solutions:
1. Use Calendar Week function
2. This is a sample formula using the transaction date:

to_char( {trandate}, 'IW' )

Basically, there are 3 to_char format models to get week number:

* W - week number in a month

* WW - week number in a year, week 1 starts at 1st of Jan

* IW - week number in a year, according to ISO standard -

In the ISO standard ( 'IW' ) , the week number can be different from the week number of the calendar year. For example, 1st Jan 1988 is in ISO week number 53 of 1987. An ISO week always starts on a Monday and ends on a Sunday.

If January 1 falls on a Friday, Saturday, or Sunday, then the ISO week that includes January 1 is the last week of the previous year, because most of the days in the week belong to the previous year.

If January 1 falls on a Monday, Tuesday, Wednesday, or Thursday, then the ISO week is the first week of the new year, because most of the days in the week belong to the new year.