TO_CHAR with dates to get week number issue
560778Oct 5 2007 — edited Jun 12 2012I 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