Re: table containing the days for a week

From: <pobox002_at_bebub.com>
Date: 24 Apr 2005 18:07:16 -0700
Message-ID: <1114391236.692176.250100@f14g2000cwb.googlegroups.com>

If you have 9i or up you could use a view like this

SQL> create or replace view last10_next30_year as   2 with calendar as

  3      (
  4      select startdate + level - 1 day
  5      from (
  6          select startdate, enddate, enddate - startdate days
  7          from (
  8              select add_months(trunc(sysdate,'y'), -120) startdate,
  9                  add_months(trunc(sysdate,'y'), 360) enddate
 10              from dual
 11              )
 12          )
 13      connect by level <= days
 14      )
 15  select
 16      day,
 17      to_char(day,'Day') dayofweek,
 18      case when to_char(day,'fmDAY') in ('SATURDAY','SUNDAY') then
 19          'Y'
 20      else
 21          'N'
 22      end weekend

 23 from calendar
 24 /

View created.

SQL> select * from (

  2      select * from last10_next30_year order by day
  3      )

  4 where rownum < 10
  5 /

DAY DAYOFWEEK W
--------- --------- -

01-JAN-95 Sunday    Y
02-JAN-95 Monday    N
03-JAN-95 Tuesday   N
04-JAN-95 Wednesday N
05-JAN-95 Thursday  N
06-JAN-95 Friday    N
07-JAN-95 Saturday  Y
08-JAN-95 Sunday    Y
09-JAN-95 Monday    N

9 rows selected.

SQL> select * from (

  2      select * from last10_next30_year order by day desc
  3      )

  4 where rownum < 10
  5 /

DAY DAYOFWEEK W
--------- --------- -

31-DEC-34 Sunday    Y
30-DEC-34 Saturday  Y
29-DEC-34 Friday    N
28-DEC-34 Thursday  N
27-DEC-34 Wednesday N
26-DEC-34 Tuesday   N
25-DEC-34 Monday    N
24-DEC-34 Sunday    Y
23-DEC-34 Saturday  Y

9 rows selected.

SQL>

-- 
MJB

Received on Sun Apr 24 2005 - 20:07:16 CDT