Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

TO_CHAR with dates to get week number issue

560778Oct 5 2007 — edited Jun 12 2012
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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 10 2012
Added on Oct 5 2007
23 comments
36,172 views