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?

for the above thread solution some expert answered as

select TO_DATE ('04-JAN-2015','DD-MM-YYYY'), to_char(TO_DATE ('04-JAN-2015','DD-MM-YYYY'), 'FMDAY') day,

       (case when to_char(trunc(TO_DATE ('04-JAN-2015','DD-MM-YYYY'), 'mm'), 'FMDAY') = 'SUNDAY' then to_number(to_char(TO_DATE ('04-JAN-2015','DD-MM-YYYY'), 'W'))

             else ceil((to_char(TO_DATE ('04-JAN-2015','DD-MM-YYYY'), 'dd') + 1 - to_char(next_day(trunc(TO_DATE ('04-JAN-2015','DD-MM-YYYY'), 'mm'), 'SUNDAY'), 'dd'))/7)

        end)+1 week_no

from dual

it determines week of month by considering Sunday to Saturday as one week and perfectly working however

how to determines week of year by considering Sunday to Saturday as one week ?