You Asked

Hi,

Can you please provide the code(procedure) for inserting the data into table with the dates only saturday and sunday from today to futuredated date.

In table i have TO_DATE column and that date will be sunday and sat only.

In this i need to insert the record for every sat and sunday from todays date to futuredated date but record insert for only sat and sunday

If you can help to provide how to calculate the sat and sunday and inserting that dates into table in loop.

Thanks in advance
sheetal

and Tom said...

.... Can you please provide the code(procedure) for inserting the data into table with the dates only saturday and sunday from today to futuredated date ...

I love requests like that.

ops$tkyte%ORA10GR2> variable todate varchar2(30)
ops$tkyte%ORA10GR2> exec :todate := '31-jan-2008'
PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> alter session set nls_date_format = 'Dy Mon DD yyyy';
Session altered.

ops$tkyte%ORA10GR2> with data as
  2  (select level-1 r, sysdate startdate
  3     from dual
  4  connect by level <= months_between( last_day(to_date(:todate,'dd-mon-yyyy')), trunc(sysdate,'mm') ) * 5 * 2
  5  )
  6  select *
  7    from (
  8  select startdate,
  9         case when mod(r,2) = 0
 10              then next_day( trunc(startdate-1) + floor(r/2)*7, 'SAT' )
 11              else next_day( trunc(startdate-1) + floor(r/2)*7, 'SUN' )
 12              end thedate
 13    from data
 14         )
 15   where thedate <= to_date(:todate,'dd-mon-yyyy')
 16   order by thedate;

STARTDATE       THEDATE
--------------- ---------------
Thu Jul 19 2007 Sat Jul 21 2007
Thu Jul 19 2007 Sun Jul 22 2007
Thu Jul 19 2007 Sat Jul 28 2007
Thu Jul 19 2007 Sun Jul 29 2007
...
Thu Jul 19 2007 Sat Jan 26 2008
Thu Jul 19 2007 Sun Jan 27 2008

56 rows selected.

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Mark, July 19, 2007 - 4:00 pm UTC

I was a little confused trying to follow your logic, so I came up with something that made more logical sense to me by returning every day between the current date and the to date in the data subquery:

1 with data as
2 (select sysdate startdate, sysdate+level-1 thedate
3 from dual
4 connect by level <= to_date(:todate,'dd-mon-yyyy') -trunc(sysdate)+1
5 )
6 select startdate,
7 thedate
8 from data where
9 to_char(thedate,'dy') in ('sat','sun')
10* order by thedate

July 19, 2007 - 7:24 pm UTC

sure that works too..

I was trying to limit the amount of work performed - so I just generated as many rows as needed (assuming 5 weeks per month times 2 days per week) and just make the saturday sundays up :)

9.0.1.4?

Steve, April 14, 2008 - 10:12 am UTC

I have this working great in my 10g database, however, I have a legacy 9.0.1.4 version. Can this be adapted to work there?

April 16, 2008 - 2:02 pm UTC

instead of the "dual connect by trick"

use all_objects - presuming the size of all_objects is big enough.

ops$tkyte@ORA9I>  with data as
  2   (select sysdate startdate, sysdate+rownum-1 thedate
  3      from all_objects
  4    where rownum     <= to_date(:todate,'dd-mon-yyyy') -trunc(sysdate)+1
  5   )
  6   select startdate,
  7          thedate
  8    from data where
  9   to_char(thedate,'dy') in ('sat','sun')
 10  order by thedate
 11  /

STARTDATE THEDATE
--------- ---------
16-APR-08 19-APR-08
16-APR-08 20-APR-08
16-APR-08 26-APR-08
16-APR-08 27-APR-08

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

9.0.1.4?

More to Explore

PL/SQL demos

PL/SQL docs

Insert the record for the every saturday and sunday as date from today to 31/01/2008

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

9.0.1.4?

More to Explore

PL/SQL demos

PL/SQL docs