Re: Week - Date function!

From: Jared Still <jkstill_at_cybcon.com>
Date: Fri, 28 Mar 2003 16:03:36 -0800
Message-ID: <F001.00575A6F.20030328160336@fatcity.com>

Maybe a slight improvement. I borrowed heavily from Jonathan Gennicks pivot table article for this. :)

http://otn.oracle.com/oramag/oracle/02-sep/o52sql.html

select x.d
from (

   select to_date(rownum, 'DDD') d,
   to_number(to_char(to_date(rownum, 'DDD'), 'IW')) week    from TABLE(pivot_package.pivot(365))
) x
where x.week = &week_num
/

The package and types appear below.

This doesn't seem to know how to deal with the 53rd week in the year. I stayed home sick today, and just don't seem to be motivated enough to fix that last bit.

Jared

drop package pivot_package;
drop type pivot_table;
drop type pivot_row;

CREATE OR REPLACE TYPE pivot_row AS OBJECT (

   x NUMBER
);
/

CREATE OR REPLACE TYPE pivot_table

   AS TABLE OF pivot_row;
/

CREATE OR REPLACE PACKAGE pivot_package AS FUNCTION pivot (num_rows IN NUMBER)

   RETURN pivot_table
   PARALLEL_ENABLE PIPELINED;
END;
/

CREATE OR REPLACE PACKAGE BODY pivot_package AS

   FUNCTION pivot (num_rows IN NUMBER)
   RETURN pivot_table PARALLEL_ENABLE PIPELINED IS

         outrow pivot_row := pivot_row (0);
      BEGIN
         FOR x IN 1..num_rows LOOP
            outrow.x := x;
            PIPE ROW(outrow);
         END LOOP;
         RETURN;
      END;

END;
/

On Friday 28 March 2003 06:38, Stephane Faroult wrote:
> SQL> l
> 1 select x.d
> 2 from (select to_date(rownum, 'DDD') d,
> 3 to_number(to_char(to_date(rownum, 'DDD'), 'IW')) week
> 4 from all_objects
> 5 where rownum < 366) x
> 6* where x.week = &week_num
>
> You may have a problem with leap years, but it's basically the idea.
> Anything smarter, somebody ?
>
> >Given a week between (1 - 52) for a particular year
> >can we get all the dates within that week. Is there
> >a function to do this
> >in SQL???
> >
> >Thanks a lot.
> >
> >Regards
> >Sesi
> >--
>
> Regards,
>
> Stephane Faroult
> Oriole

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  INET: jkstill_at_cybcon.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).

Received on Fri Mar 28 2003 - 18:03:36 CST