Missing Dates between two dates in sql

Question

I want to find the missing dates between two dates. In the table there is a field called date in that it present

2018-11-13, 2018-11-18, 2018-11-20, 2018-11-25

I want the missing dates from 2018-11-13 to 2018-11-25

Expected Output
2018-11-14
2018-11-15
2018-11-16
2018-11-17
2018-11-19
2018-11-21
2018-11-22
2018-11-23
2018-11-24

Do you have any calendar dates table to find out missing dates between the date range? — Avi, Nov 29 '18 at 04:32
@VenkataramanR it show all the missing days between the dates...i want if in between 2018-11-13 and 2018-12-01 there is a date 2018-11-18 is present in the database the query wil skip that date and show rest missing dates — Lima, Nov 29 '18 at 04:54
@Lima, can you please provide your database schema and also, update your question. The current question is misleading. — Venkataraman R, Nov 29 '18 at 05:06
@Squirrel its not duplicate its a different question as missinig date cant be in the table — Nikhil S, Nov 29 '18 at 10:38

Nikhil S · Answer 1 · 2018-11-29T10:36:11.547

this will surely work:

        select * from date_ranges;
        13-NOV-18
        01-DEC-18
        16-NOV-18
        20-NOV-18

        set serveroutput on;
create or replace procedure date_range( d1 date, d2 date) is 
op date;
op2 date;
diff number;
comp date;
a  number;
b number;
j number;
cursor c1 is select a from date_ranges;
begin
select d2-d1 into diff from dual;
for op in 1..diff 
loop <<forloop>>
j:=0;
select d1+op into op2 from dual;

          open c1;
          loop <<curloop>>
          fetch c1 into comp;
          if(c1%notfound) then
          exit;
          end if;
          select sysdate-to_date(comp) into a from dual;
          select sysdate-to_date(op2)  into b from dual;
          if(a=b)then 
          j:=1;
          end if;

          end loop  curloop;

          close c1;
if(j=0)then 
DBMS_OUTPUT.PUT_LINE(op2);
end if;
end loop forloop;
end;
/

declare
m date;
n date;
begin 
select min(a) into m from date_ranges;
select max(a)  into n from date_ranges;
date_range(m,n);
end;
/

output:

14-NOV-18
15-NOV-18
17-NOV-18
18-NOV-18
19-NOV-18
21-NOV-18
22-NOV-18
23-NOV-18
24-NOV-18
25-NOV-18
26-NOV-18
27-NOV-18
28-NOV-18
29-NOV-18
30-NOV-18

so dates 16 nov and 20 nov are not printed since they were present in queried table and missing dates printed between min and max of the sample date. thank you!!!!!!!!!!!!!!!1

it show all the missing days between the dates...i want if in between 2018-11-13 and 2018-12-01 there is a date 2018-11-18 is present in the database the query wil skip that date and show rest missing dates — Lima, Nov 29 '18 at 05:06

Avi · Answer 2 · 2018-11-29T05:20:26.813

-1

You can make a use of While loop if you would like to store missing dates in some table. In this way also it will work.

Declare @mindate date ='2018-11-13' 
Declare @maxdate date = '2018-12-01' 

select '2018-11-18' as Existingdate into #tmp  --made existing dates 
create table Missingdates (Missingdate date) 

Declare @id int = 1 
While  dateadd(day, @id, @mindate) > @mindate and dateadd(day, @id, @mindate) < @maxdate  
Begin 
insert into Missingdates  select dateadd(day, @id, @mindate) as MIssingDate  where not exists (Select  * from #tmp t where t.Existingdate = dateadd(day, @id, @mindate)) 
set @id = @id+1 
end 

select * from Missingdates

Output:

Missingdate
2018-11-14
2018-11-15
2018-11-16
2018-11-17
2018-11-19
-- so on

Added not exists clause and not it will not give existing dates.

edited Nov 29 '18 at 05:20

answered Nov 29 '18 at 04:53

Avi

1,795
3
16
29

it show all the missing days between the dates...i want if in between 2018-11-13 and 2018-12-01 there is a date 2018-11-18 is present in the database the query wil skip that date and show rest missing dates – Lima Nov 29 '18 at 05:05
@Lima This query will check if the date exists in table or not, and then insert missing dates. This will not give 2018-11-18, and return all missing dates. – Avi Nov 29 '18 at 05:21

Missing Dates between two dates in sql

2 Answers2