SQL & PL/SQL

I am trying to build a report showing weekly sales data and running into an issue with Dates and TO_CHAR..

Using the date 9/29/2007, I expect the return from the following to be 4:

SELECT TO_CHAR('09/29/2007','W') FROM DUAL

However, it returns 5!!! Testing further I tried the next day, since it is a Sunday, it should be the start of the next week, 5..

SELECT TO_CHAR('09/30/2007','W') FROM DUAL.. I do get a 5..

I understand that the implementation of TO_CHAR with the 'W' parameter defaults a week start on what ever the first day of the month is, but I need a method that is more calendar like (Sunday is the start of a week, Saturday end of the week).

Anyone have a nifty solution?

Thank you,

Tony Miller
UTMB/EHN