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 -> table containing the days for a week

table containing the days for a week

From: yves ludwig <yve_at_free.fr>
Date: Sat, 23 Apr 2005 04:36:44 +0200
Message-ID: <5xiae.49034$Of5.31883@nntpserver.swip.net>


In one of my application, I need to have a table containing all the days of a week.

Therefore, I created a table myCalendar containing one row / day for the last 10 years and the next 30 years and perform the following select

         SELECT myDate from myCalendar where TO_CHAR(myDate,"IW")="16" and TO_CHAR(myDate,"IYYY")="2005"

that returns

          18/04/2005
          19/04/2005
          ...
          24/04/2005


I have two questions

(1) How could this select be improved ?

    I think that Oracle (or any DBMS) needs to generate the strings for my 40 years and search the "16" and "2005" values. In my application, I currently have added in the myCalendar table two columns : myYear and myWeek containing the numeric values that are calculated only one time : during the creation of the table. Therefore my select is currently
SELECT myDate from myCalendar where myWeek=16 and myYear=2005

(2) Is it possible to work without any 'myCalendar' table to obtain the
result ?

I remember I read an article concerning this topic in an old Oracle Magazine a few years ago (during winter 2002 or 2001)... {one should really keep everything).

Thanks for help & best regards, Yves Received on Fri Apr 22 2005 - 21:36:44 CDT

Original text of this message

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