Q
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);
 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 last published in June 2002

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close