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
kuwait
Messages: 52
Registered: October 2007
Member
Hi

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?

Thanks
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: 64131
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.

Regards
Michel
Re: date & calendar table [message #356924 is a reply to message #356911] Mon, 03 November 2008 04:08 Go to previous message
JRowbottom
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
begin
  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;
  return;
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: Wed Dec 07 12:43:32 CST 2016

Total time taken to generate the page: 0.14171 seconds