Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Weeknumber of a year

Re: Weeknumber of a year

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 20 Jul 2006 03:52:13 -0700
Message-ID: <1153392733.544829.291920@i3g2000cwc.googlegroups.com>

Sybrand Bakker wrote:
> On 19 Jul 2006 13:23:32 -0700, purush.venkat_at_gmail.com wrote:
>
> >Hi Experts,
> >
> >I have a question where in i have to get a weeknumber as follows :
> >
> >Week Week Ending
> >
> >1 1/5/2006
> >2 1/12/2006
> >3 1/19/2006
> >4 1/26/2006
> >5 2/2/2006
> >6 2/9/2006
> >
> >etc. so on and so forth.
> >
> >Can anyone help me with this in ORACLE 9i please?
> >
> >Thanks,
> >-- Tom .
>
> Please look up the WW date format model and the IW format model under
> 'Date format models' in the 'SQL reference manual'
>
> Please avoid asking doc questions, try to be somewhat more industrious
> and search on http://tahiti.oracle.com *prior* to posting.
>
>
> --
> Sybrand Bakker, Senior Oracle DBA

Test run comparing WW, IW, and calculated method that I posted, with the week ending on Thursdays per the original request (note: substitute any other table with at least 300 rows for the PART table in the query below):

SELECT

  TO_DATE('12/31/2005','MM/DD/YYYY')+ROWNUM CHK_DATE,
  TO_CHAR(TO_DATE('12/31/2005','MM/DD/YYYY')+ROWNUM,'WW') WW,
  TO_CHAR(TO_DATE('12/31/2005','MM/DD/YYYY')+ROWNUM,'IW') IW,

CEIL(((TO_DATE('12/31/2005','MM/DD/YYYY')+ROWNUM)-TO_DATE('01/05/2006','MM/DD/YYYY'))/7)+1 CALC
FROM
  PART
WHERE
  ROWNUM<301;

CHK_DATE	WW	IW	CALC
1-Jan-06	01	52	1
2-Jan-06	01	01	1
3-Jan-06	01	01	1
4-Jan-06	01	01	1
5-Jan-06	01	01	1
6-Jan-06	01	01	2
7-Jan-06	01	01	2
8-Jan-06	02	01	2
9-Jan-06	02	02	2
10-Jan-06	02	02	2
11-Jan-06	02	02	2
12-Jan-06	02	02	2
13-Jan-06	02	02	3
14-Jan-06	02	02	3
15-Jan-06	03	02	3
16-Jan-06	03	03	3
17-Jan-06	03	03	3
18-Jan-06	03	03	3
19-Jan-06	03	03	3
20-Jan-06	03	03	4
21-Jan-06	03	03	4
22-Jan-06	04	03	4
23-Jan-06	04	04	4
24-Jan-06	04	04	4
25-Jan-06	04	04	4
26-Jan-06	04	04	4
27-Jan-06	04	04	5
28-Jan-06	04	04	5
29-Jan-06	05	04	5
30-Jan-06	05	05	5
31-Jan-06	05	05	5
1-Feb-06	05	05	5
2-Feb-06	05	05	5
3-Feb-06	05	05	6
4-Feb-06	05	05	6
5-Feb-06	06	05	6
6-Feb-06	06	06	6
7-Feb-06	06	06	6
8-Feb-06	06	06	6
9-Feb-06	06	06	6
10-Feb-06	06	06	7

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Thu Jul 20 2006 - 05:52:13 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US