| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Simple Query for Week number
Thanks Yechiel. I missed that. That is a life saver.
"Yechiel
Adar" To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
<adar76_at_inter cc:
.net.il> Subject: Re: Simple Query for Week number
Sent by:
root_at_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 <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 Queryfor
> 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,
> 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-LReceived on Thu Dec 19 2002 - 14:30:38 CST
(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).
 |
 |