Requires Free Membership to View
One way to generate dates is to use a Calendar table. This is just another ordinary table that you can create, which will hold the dates you want. Calendar tables are useful for date calculations involving holidays, business days, etc. One of my previous answers, Difference between two dates using office hours (22 May 2001), has an example. You would need to populate your Calendar table with as many dates as you want to cover in the date range.
Another way to generate dates is with an Integer table. This might be easier if all you want is the dates themselves, and no ancillary information about each date.
create table integers
(i integer);
insert into integers values (0);
insert into integers values (1);
insert into integers values (2);
insert into integers values (3);
insert into integers values (4);
insert into integers values (5);
etc.
Populating this table with as many entries as you require should not be very difficult, and you'd only need to do it once, after which you could use it over and over on any number of date ranges.
select TO_DATE('2002-07-01','YYYY-MM-DD') + i
from integers
where TO_DATE('2002-07-01','YYYY-MM-DD') + i
<= TO_DATE('2002-09-30','YYYY-MM-DD')
This query will return all dates between July 1 and September 30, inclusive (assuming there are enough integers in the table).
This was first published in June 2002
Join the conversationComment
Share
Comments
Results
Contribute to the conversation