Thanks Yechiel. I missed that. That is a life saver.
"Yechiel
Adar" To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
<adar76@inter cc:
.net.il> Subject: Re: Simple Query for Week number
Sent by:
root@fatcity.
com
December 19,
2002 01:52 PM
Please
respond to
ORACLE-L
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 <[EMAIL PROTECTED]>
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@mdx To: Multiple recipients of
list ORACLE-L <[EMAIL PROTECTED]>
> .com> cc:
> Sent by: Subject: RE: Simple Query
for
Week number
> root@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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
