Help! SQL*Plus date conversion flaky?

From: Steve Frampton, Computer Operator/Systems Clerk <frampton_at_admin.flarc.edu.on.ca>
Date: 1995/10/20
Message-ID: <468e6b$hpc_at_govonca3.gov.on.ca>#1/1

Hello:

A colleague of mine is writing an SQL*Report. Given a date, she needs to know the week # of the month the day falls on (eg. October 27, 1995 should fall on week #4).

She thought of using something like:

     select to_char(to_date('&start_date','DD-MON-YYYY'),'W')
     from   dual;

However, this is not working as we had expected it would. For example, in September 1995, week #1 should be from the 1-2, #2 from 3-9, #3 from 10-16, #4 from 17-23, and #5 from 24-30. However, the "W" date conversion seems to consider a week to start, on the very day the month starts (eg. for September, the 1st falls on a Friday, therefore the week spans returned by the "W" conversion incorrectly reports the week #'s as (in the case of September), #1: 1-7, #2: 8-14, #3: 15-21, #4: 22-28, #5: 29-30.

I can't think of any possible reason why anybody would want results like these. Is this a bug or a legitimate feature? Is there a better way to achieve the results we are looking for?

Any information would be greatly appreciated.

Thanks in advance.

---

This article contains my own words formed by my own opinions. I speak on my own behalf and my views do not necessarily agree with those of my employer.
-----------------------------------+-------------------------------------------

Steve Frampton                     | Phone:  (613) 544-4927, extension 331

Computer Operator/Systems Clerk | Fax: (613) 530-4761 Frontenac-Lennox & Addington RCSSB | E-mail: frampton_at_admin.flarc.edu.on.ca
-----------------------------------+-------------------------------------------
Received on Fri Oct 20 1995 - 00:00:00 CET