How do I calulate week of year in Oracle using a non-standard first day of the week?

Question

I have a query that needs to return the "week of year" of a date field but the customer of the query uses a non-standard first day of the week so TO_CHAR with 'IW' isn't returning the expected result. In this case the first day of the week is Saturday and Friday is the seventh day of the week.

With T-SQL I'd use DATEPART and SET DATEFIRST.

What is the Oracle equivalent? The Oracle answers I've found in the google all talk about setting the NLS_TERRITORY like so ALTER SESSION SET NLS_TERRITORY = 'UNITED KINGDOM'; but I'm not seeing where I can pick an arbitrary day (other than perhaps finding a territory that uses Saturday).

Answer 1

IW works with a Monday - Sunday week, so this should get you what you are looking for. Basically, get the week according to the day 2 days from now:

to_char(your_date + 2, 'IW')

Answer 2

Oracle's default week format calculates the week number from the first day of the year instead of the first day of the week.

So if a year starts on 01-jan-2009 and the first day is on Wednesday, the week No. 1 will be from 01-jan-2009 to 08-jan-2009 (wednesday to tuesday).

You can use the "iw" format (and a little tweak) if you need the week range to start from sunday through saturday. http://download-uk.oracle.com/docs/cd/B14117_01/server.101/b10749/ch9sql.htm#CIHGFJEI

Try this code below. I basically use the "IW" format and add a condition to get the week number to start from a given date.. say...01-jul-2008.

select target_date, 
        to_char(target_date+1,'iw') week_sun_thru_saturday,
        to_number(to_char(target_date+1,'iw')) -
        to_number(to_char( to_date('10-jul-2008','dd-mon-yyyy')+1,'iw')) week_from_01_jul_2008
from t;

Remember..This code will not give week number 1 from jul1st to jul-07 .unless of course 01-jul-2008 is a sunday ;)