8

How do I select dates between two given dates in an Oracle query?

Vadzim
  • 24,954
  • 11
  • 143
  • 151
Prabakaran
  • 221
  • 4
  • 9
  • 19
  • See also [Generate a range of dates using SQL](https://stackoverflow.com/questions/418318/generate-a-range-of-dates-using-sql) – Vadzim May 13 '20 at 16:21

7 Answers7

17
SELECT TO_DATE('12/01/2003', 'MM/DD/YYYY') - 1 + rownum AS d
FROM all_objects
WHERE TO_DATE('12/01/2003', 'MM/DD/YYYY') - 1 + rownum <= TO_DATE('12/05/2003', 'MM/DD/YYYY')

from

http://forums.devshed.com/oracle-development-96/select-all-dates-between-two-dates-92997.html

Greg Reynolds
  • 9,736
  • 13
  • 49
  • 60
  • -1 This code example (from the linked page) is answering a slightly different question, and is more complicated than needed for the question asked here. – payne Feb 18 '11 at 12:28
  • @payne, Greg has given the best answer so far. All others *assume* that there is a table involved. Granted, that's possibly what the OP *wanted* (as opposed to *did*) ask, but then again, if someone is too lazy to state exactly what he wants, he should get what he asked for. Therefore +1 – René Nyffenegger Feb 18 '11 at 12:32
  • I guess with the slightly vague requirement it wasn't clear that the OP wanted to select from a table - I guess we'll find out if and when an answer is accepted. – Greg Reynolds Feb 18 '11 at 14:34
  • 2
    Anything based on a table is predicated on the notion that your date range will be less than the number of rows in the table. If you happen to pass in a big date range this solution fails. Which is why using the LEVEL pseudocolumn like my solution is preferable as it is valid for any arbitrarty range. – Michael Broughton Feb 18 '11 at 14:46
  • suppose if i need to take startdate as sysdate(current date) instead of specific date what can i do? – Prabakaran Feb 19 '11 at 04:47
  • Just replace the to_date('12/01/2003', 'MM/DD/YYYY') entries with current_timestamp – Greg Reynolds Feb 22 '11 at 10:58
6
SELECT * FROM your_table WHERE your_date_field BETWEEN DATE '2010-01-01' AND DATE '2011-01-01';
diagonalbatman
  • 17,340
  • 3
  • 31
  • 31
  • Who said anything about Jan 1st 2010 to Jan 1st 2011, I just aswell might ask. What is the difference??? sysdate is a date just as Jan 1st 2010 – Klaus Byskov Pedersen Feb 18 '11 at 12:30
  • well because sysdate is slightly different to a usual date, and without explaination of what sysdate does - it would make more sense to stick with a traditional date. Nothing wrong with SysDate - but it needs a bit more explanation that you provided. – diagonalbatman Feb 18 '11 at 12:32
  • @Ronnis - rather than just down voting how about you answer the question and supply an alternative? – diagonalbatman Feb 18 '11 at 16:02
  • @Andy Paton, I'd rather upvote yours if you used to_Date or DATE literals. – Ronnis Feb 18 '11 at 16:09
  • 1
    ok you two, stop fighting or I'll take your toys away... and downvote you both. – Jeffrey Kemp Feb 19 '11 at 09:23
5

You can use the LEVEL pseudocolumn in a tricky way to generate a series, so, for example, to get the list of days between today and 20 days from now I can:

select trunc(sysdate+lvl) from
  (select level lvl from dual connect by level < ((sysdate+20)-sysdate - 1) )
order by 1

Generically you can see how this would apply for any two given dates.

select trunc(early_date+lvl) from
  (select level lvl from dual connect by level < (later_Date-early_date-1) )
order by 1

And you can adjust the clauses if you want to include the two end dates as well.

Ronnis
  • 12,593
  • 2
  • 32
  • 52
Michael Broughton
  • 4,045
  • 14
  • 12
1

You could also use the below to get a list of calendar dates between a date range (similar to Michael Broughton's solution)

select (trunc(sysdate) - (trunc(sysdate) - (to_date('start_date')))) -1 + level  from dual
connect by level <= 
((select (trunc(sysdate) - (trunc(sysdate) - (to_date('end_date'))))-
(trunc(sysdate) - (trunc(sysdate) - (to_date('start_date'))))from dual)+1);
Daniel Oranye
  • 11
  • 2
0

I do this so often for a scheduling app I work on that I created a pipelined table function. Sometimes I need days, hours or 15 minutes between times. This is not exactly my function, because my code is in a package. For example, here, I'm getting days between Jan 1 2020 and Jan 10 2020:

SELECT
    days.date_time
FROM
    table(between_times(TO_DATE('2020-01-01'),TO_DATE('2020-01-10'),(60*24), 'Y')) days

The pipelined function:

function between_times(i_start_time TIMESTAMP, i_end_time TIMESTAMP, i_interval_in_minutes NUMBER, include_end_time VARCHAR2 := 'N')
  RETURN DateTableType  PIPELINED
  AS
    time_counter TIMESTAMP := i_start_time;
  BEGIN
    IF i_start_time IS NULL OR i_end_time IS NULL or i_start_time > i_end_time OR i_interval_in_minutes IS NULL OR
      i_interval_in_minutes <= 0 THEN
        RETURN;
      END IF;
      LOOP

        -- by default does not include end time
        if (include_end_time = 'Y') THEN
          exit when time_counter > i_end_time;
        ELSE
          exit when time_counter >= i_end_time;
        END IF;
        
        
        pipe row(DateType( time_counter ));     
        time_counter := time_counter + i_interval_in_minutes/(60*24);
        
      END LOOP;
      
      EXCEPTION  WHEN NO_DATA_NEEDED THEN NULL;      
  END;
dougd_in_nc
  • 371
  • 5
  • 20
-1

with all_days as (select trunc(to_date('12-03-2017','dd-mm-yyyy')+levl)-1 as all_dates from (select level levl from dual connect by level < (sysdate-to_date('12-03-2017','DD-MM-YYYY')+1) ) order by 1) select count(*) as no_of_days from all_days where ltrim(rtrim(to_char(all_dates,'DAY'))) not in ('SATURDAY','SUNDAY');

Ram Yerra
  • 1
-1

Use "between". In a general sense:

select * from someTable where dateCol between date1 and date2;

note that dateCol is defined as a date and date1 and date2 are also date values. If these aren't dates, then you'll convert them to dates using to_date function.

tbone
  • 15,107
  • 3
  • 33
  • 40