Oracle-L: Re: Simple Query for Week number

From: Yechiel Adar <adar76_at_inter.net.il>
Date: Thu, 19 Dec 2002 10:52:34 -0800
Message-ID: <F001.0051EAD6.20021219105234@fatcity.com>

Check out the date 19/11/2002. format 'W' return 3 instead of 4.

I wrote something to do this. It select the week day number of the current date,
the week day number of the first of the month and the current week. If week day of the first of the month is higher then the current week day Then add 1 to the current week.

SELECT CASE WHEN CUR_DAY < FIRST_DAY

    THEN CUR_WEEK + 1
    ELSE CUR_WEEK + 0
       END CASE
FROM

 (SELECT TO_CHAR(TO_DATE('07/12/2002','DD/MM/YYYY'),'D') CUR_DAY FROM DUAL),
(SELECT TO_CHAR( TO_DATE('01/' || to_char(last_day(TO_DATE
        ('07/12/2002','DD/MM/YYYY')),'MM/YYYY'),'DD/MM/YYYY'),'D') FIRST_DAY

from dual) ,
(SELECT TO_CHAR(TO_DATE('07/12/2002','DD/MM/YYYY'),'W') CUR_WEEK FROM DUAL) ;

Yechiel Adar
Mehish
----- Original Message -----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Thursday, December 19, 2002 7:59 PM

>
> Dan, Thanks. The "W" works just fine. What I was looking for.
>
> Phew!!! I almost wrote a PL/SQL for this ;))))
>
>
>
>
>
> "Fink, Dan"
> <Dan.Fink_at_mdx To: Multiple recipients of
list ORACLE-L <ORACLE-L_at_fatcity.com>
> .com> cc:
> Sent by: Subject: RE: Simple Query for
Week number
> root_at_fatcity.
> com
>
>
> December 19,
> 2002 12:05 PM
> Please
> respond to
> ORACLE-L
>
>
>
>
>
>
> Raj,
> How do you define week number? If a month starts in the middle
> of
> the week, is that week #1 or must it be the first full week?
> If the format mask 'W' is adequate, use
>
>
> 1 select sysdate, to_char(sysdate, 'W'),
> 2 '01-DEC-02', to_char(to_date('01-DEC-02'), 'W'),
> 3 '30-NOV-02', to_char(to_date('30-NOV-02'), 'W')
> 4* from dual
> SQL> /
>
> SYSDATE T '01-DEC-0 T '30-NOV-0 T
> --------- - --------- - --------- -
> 19-DEC-02 3 01-DEC-02 1 30-NOV-02 5
>
>
>
> -----Original Message-----
> Sent: Thursday, December 19, 2002 8:54 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Hey Folks,
>
> Looking for the simplest of queries to find the week number, given a date.
> For example, 19th December 2002 falls in the 3rd week, whereas 19th
> November, 2002, falls in the 4th week.
>
> Thanks
> Raj
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author:
> INET: Rajesh.Rao_at_jpmchase.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Fink, Dan
> INET: Dan.Fink_at_mdx.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author:
> INET: Rajesh.Rao_at_jpmchase.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Yechiel Adar
  INET: adar76_at_inter.net.il

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Thu Dec 19 2002 - 12:52:34 CST