Home » SQL & PL/SQL » SQL & PL/SQL » date & calendar table
date & calendar table [message #356911] Mon, 03 November 2008 01:50 Go to next message
Messages: 52
Registered: October 2007

I have a table with entries that have a date attribute i want to know which dates were not entered for a specific period
Is there a predefined calendar table?

Re: date & calendar table [message #356918 is a reply to message #356911] Mon, 03 November 2008 02:21 Go to previous messageGo to next message
Michel Cadot
Messages: 65086
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe if you searched for "calendar" before posting you'd already get the answer.

Re: date & calendar table [message #356924 is a reply to message #356911] Mon, 03 November 2008 04:08 Go to previous message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you fancy creating a generic calendar , you can do it like this:
create or replace type ty_calendar as table of date;

create or replace function calendar(p_from_date in date, p_to_date in date) return ty_calendar pipelined as
  for rec in (select trunc(p_from_date) + level -1 dte from dual connect by level <= p_to_date+1 - p_from_date) loop
    pipe row(rec.dte);
  end loop;
end calendar;

select * from table(calendar(sysdate-10,sysdate+3));
Previous Topic: Rows to columns conversionand inserting records
Next Topic: BULK insert
Goto Forum:

Current Time: Tue Jul 25 06:16:54 CDT 2017

Total time taken to generate the page: 0.07972 seconds