Re: DateTime Format Elements

Just a little bit of date manipulations will get you the results you want... Here:

SELECT
   mydate,
   TRUNC((mydate + 7 -
   next_day(last_day(add_months(mydate, -1))+1, 'MONDAY')) /7) + 1 "Week#"
FROM
  dttest

I made the assumption that hte week starts on Monday. You can change that if you want, just replace with the appropriate day-of-the-week.

JP

bwall-99_at_alumni.calpoly.edu (Brian D. Wall) wrote in message news:<e4a4deb1.0205311547.265750b_at_posting.google.com>...
> I was looking for a way to find what week of the month a day falls on, but
> found it wasn't working like I expected. Here's an example:
>
> EXPECTED RESULTS:
> May 1, 2002 is a Wednesday, so I would expect the following Results:
> Week #1 = Wed. May 1 - Sat. May 4
> Week #2 = Sun. May 5 - Sat. May 11
> etc...
>
> ACTUAL RESULTS:
> The results I get are as follows:
> Week #1 = Wed. May 1 - Tues. May 7
> Week #2 = Wed. May 8 - Tues. May 14
> etc...
>
> What I find interesting is that the Week of Year function behaves like I
> would expect the Week of Month to behave. The first week of 2002 is Jan 1 -
> 5, not Jan 1 - 7. Why the inconsistency?
>
> Brian
Received on Mon Jun 03 2002 - 15:38:06 CEST