Return-Path: Received: from ensim.rackshack.net (root@localhost) by orafaq.net (8.11.6/8.11.6) with ESMTP id gBJN8sJ17392 for ; Thu, 19 Dec 2002 17:08:54 -0600 X-ClientAddr: 209.68.248.164 Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by ensim.rackshack.net (8.11.6/8.11.6) with ESMTP id gBJN8p317380 for ; Thu, 19 Dec 2002 17:08:51 -0600 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id LAA01926; Thu, 19 Dec 2002 11:51:34 -0800 (PST) Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 0051EC3B; Thu, 19 Dec 2002 11:42:07 -0800 Message-ID: Date: Thu, 19 Dec 2002 11:42:07 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: "Fink, Dan" Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: "Fink, Dan" Subject: RE: Simple Query for Week number Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Yechiel, Excellent! It all depends on how 'WEEK' is defined. (Shades of our former President). Oracle appears to define it as 7 day increments. So, using November as an example. Nov 1, 2 - Week 1? or Nov 1 - 7 Nov 3 - 9 - Week 2? or Nov 8 - 14 One of those vexing business questions. My rule of thumb is, whatever you code, the business side will say is wrong. When you fix the code, the business side will say that is wrong as well... Dan -----Original Message----- Sent: Thursday, December 19, 2002 11:53 AM To: Multiple recipients of list 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 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" > > .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: Rajesh.Rao@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@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@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@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@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@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@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@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@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@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).