Problem solve Get help with specific problems with your technologies, process and projects.

Finding all the dates between two dates

I want to get all the dates falling between 2 different dates in an SQL query (Oracle). In a database, it shows...

the dates corresponding to which records are available, but I want all the dates whether or not a record is available.

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);

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).

Dig Deeper on Oracle and SQL