RE : Selecting from Calendar ??

From: Stephane Faroult <sfaroult_at_oriolecorp.com>
Date: Thu, 29 Jun 2000 15:44:40 +0200
Message-Id: <10543.110761@fatcity.com>

Raj,

   Is it what you need ?

SQL> select 2000 "YEAR",

  2         obj# "MONTH",
  3         to_date('01/'||to_char(obj#)||'/2000', 'DD/MM/YYYY')
"START",
  4         last_day(to_date('01/'||to_char(obj#)||'/2000',

'DD/MM/YYYY')) "END"
,
  5 1+trunc(to_date('01/'||to_char(obj#)||'/2000', 'DD/MM/YYYY'), 'D')

  6                                    "START2",
  7         trunc(last_day(to_date('01/'||to_char(obj#)||'/2000',

'DD/MM/YYYY'))
,

  8                  'D') "END2"

  9 from sys.obj$
 10 where obj# < 13
 11 /

      YEAR MONTH START END START2 END2

---------- ---------- ---------- ---------- ---------- ----------
      2000          1 01-01-2000 01-31-2000 12-27-1999 01-30-2000
      2000          2 02-01-2000 02-29-2000 01-31-2000 02-27-2000
      2000          3 03-01-2000 03-31-2000 02-28-2000 03-26-2000
      2000          4 04-01-2000 04-30-2000 03-27-2000 04-30-2000
      2000          5 05-01-2000 05-31-2000 05-01-2000 05-28-2000
      2000          6 06-01-2000 06-30-2000 05-29-2000 06-25-2000
      2000          7 07-01-2000 07-31-2000 06-26-2000 07-30-2000
      2000          8 08-01-2000 08-31-2000 07-31-2000 08-27-2000
      2000          9 09-01-2000 09-30-2000 08-28-2000 09-24-2000
      2000         10 10-01-2000 10-31-2000 10-02-2000 10-29-2000
      2000         11 11-01-2000 11-30-2000 10-30-2000 11-26-2000
      2000         12 12-01-2000 12-31-2000 11-27-2000 12-31-2000

12 rows selected.

-- 
Regards,

  Stephane Faroult
  email: sfaroult_at_oriolecorp.com 
  Oriole Corporation
  Voice:  +44  (0) 7050-696-269 
  Fax:    +44  (0) 7050-696-449 
  Performance Tools & Free Scripts
------------------------------------------------------------------
http://www.oriolecorp.com, designed by Oracle DBAs for Oracle DBAs
------------------------------------------------------------------


> 

> Hi all,

> 

> I have a Calendars table ... where we save month_start and month_end date.

> The start/end dates of these months have no relationship with actual months

> in Normal (Gregorian) calendars. 

> 

> What I need is a list of month_num, month_start and month_end dates when I

> provide a start and end date.

> 

> The table structure is table_cal (year, month, month_start, month_end), for

> the same of simplicity let's assume this table contains gregorian calendar.

> 

> e.g. (based on gregorian calendar_ when I enter 01-15-2000 to 05-10-2000 

> Expected Result

> YEAR    MONTH   START           END

> ----------------------------------------

> 2000    1       01-01-2000              01-31-2000

> 2000    2       02-01-2000              02-29-2000

> 2000    3       03-01-2000              03-31-2000

> 2000    4       04-01-2000              04-30-2000

> 2000    5       05-01-2000              05-31-2000

> 

> BTW I can't make use of LAST_DAY or FIRST_DAY as the start /end dates for

> our months are different from those of Gregorian Calendar months. We use

> Broadcast calendars, where a month ends on the last sunday e.g. Jun 2000 is

> May29 - Jun25 and Jul 2000 is Jun26 - Jul30. I have been trying to get this

> in SQL for a day, but no avail ... I know this can be done in pl/sql, but if

> I could do this in SQL, it would help as lots of processing needs to be done

> based on this logic.

> 

> Your help is greatly appreciated

> TIA

> Raj

> ______________________________________________________

> Rajendra Jamadagni              MIS, ESPN Inc.

> Rajendra dot Jamadagni at ESPN dot com

> QOTD: Any clod can have facts, but having an opinion is an art !

Received on Thu Jun 29 2000 - 08:44:40 CDT