Re: Need a DATE script that produces....

From: Jurij Modic <jmodic_at_src.si>
Date: 1999/02/19
Message-ID: <36cddf18.11159683_at_news.siol.net>#1/1

On 18 Feb 1999 19:55:34 GMT, farnham_at_spot.Colorado.EDU (Jenny Farnham) wrote:

>
>I need a sql*plus script that will produce these results
>for a calendar year of 1999. I need the first day of the
>business week (Monday) and the last day of the week (Friday).
>I could create a table/view and hand load these in, but was
>wondering if anyone could come up with a script to generate
>these dates using sql*plus.
>
>If you create it, could you post it here? or give URL for the
>web site of where we could download it? Thanks!
>
>First Day of Week Last Day of Week
>----------------- -----------------
>04-JAN-1999 08-JAN-1999
>11-JAN-1999 15-JAN-1999
>18-JAN-1999 22-JAN-1999
>25-JAN-1999 29-JAN-1999
>01-FEB-1999 05-FEB-1999
>08-FEB-1999 12-FEB-1999
You need to select from a table that have more than 52 records. For example, a view ALL_OBJECTS, accesible to every user regardles of their privileges, will allways contain a lot more rows than this limit. One of the quite simple sullution would then be:

SQL> SELECT
  2 (rownum-1)*7 +
  3 NEXT_DAY(TO_DATE('31121998','ddmmyyyy'),'MONDAY') bow,   4 rownum*7 +
  5 NEXT_DAY(TO_DATE('31121998','ddmmyyyy'),'MONDAY')-3 eow   6 FROM all_objects
  7 WHERE rownum*7 +

  8          NEXT_DAY(to_date('31121998','ddmmyyyy'),'MONDAY')-3 <=
  9        TO_DATE('31121999','DDMMYYYY');

BOW      EOW

-------- --------

04.01.99 08.01.99
11.01.99 15.01.99
18.01.99 22.01.99
25.01.99 29.01.99
01.02.99 05.02.99
08.02.99 12.02.99
15.02.99 19.02.99
22.02.99 26.02.99
01.03.99 05.03.99
....
29.11.99 03.12.99
06.12.99 10.12.99
13.12.99 17.12.99
20.12.99 24.12.99
27.12.99 31.12.99

52 rows selected.

SQL> HTH,
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)

---

The above opinions are mine and do not represent any official standpoints of my employer Received on Fri Feb 19 1999 - 00:00:00 CET