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 .

purush.venkat (AT) gmail (DOT) com wrote:
Starting point - experiment:
Find the number of days, divide by 7, round up, add 1 to the result:
SELECT
CEIL((TRUNC(SYSDATE)-TO_DATE('01/05/2006','MM/DD/YYYY'))/7)+1
FROM
DUAL;

Plugging in a date in place of the current date in the previous
statement:
SELECT

CEIL((TO_DATE('01/04/2006','MM/DD/YYYY')-TO_DATE('01/05/2006','MM/DD/YYYY'))/7)+1
FROM
DUAL;

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.

On 19 Jul 2006 13:23:32 -0700, purush.venkat (AT) gmail (DOT) com wrote:

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

Sybrand Bakker wrote:
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.