Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Weeknumber of a year
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