get miss date between to dates using mysql

Question

Get miss date between a set of date. I want to get miss date between from date and to date. Am new for mysql please help me to get miss date between to dates.

Answer 1

There's a similar tread here: Get a list of dates between two dates

Answer 2

This answer is lovingly ripped off paxdiablo's answer from the answer referenced above.

All I have done is added some test data and an example query that uses a left outer join to find missing dates. I have used the test data in your comment i.e.

"suppose my table have 01-01-2012,01-02-2012,01-04-2012,01-05-2012 and 01-07-2012. Now i want to get 01-03-2012 and 01-06-2012. How can i get this"

You just got to work out a way of populating the all_dates table. It kinda depends on what constitutes a 'full set of dates' for you. Every date into the future? The first day of every month into the future? Every date from the past and the future? The first day of every month in the past and the future? Up to you here. I have used the first day of every month in 2012 as an example:

create table all_dates
(id int unsigned not null primary key auto_increment,
a_date date not null,
unique key `all_dates_uidx1`(a_date));

-- First day of every month in 2012    
insert into all_dates (a_date) values ('2012-01-01');
insert into all_dates (a_date) values ('2012-02-01');
insert into all_dates (a_date) values ('2012-03-01');
insert into all_dates (a_date) values ('2012-04-01');
insert into all_dates (a_date) values ('2012-05-01');
insert into all_dates (a_date) values ('2012-06-01');
insert into all_dates (a_date) values ('2012-07-01');
insert into all_dates (a_date) values ('2012-08-01');
insert into all_dates (a_date) values ('2012-09-01');
insert into all_dates (a_date) values ('2012-10-01');
insert into all_dates (a_date) values ('2012-11-01');
insert into all_dates (a_date) values ('2012-12-01');

-- Note that my_dates table is missing 2012-03-01 and 2012-06-01
create table my_dates
(id int unsigned not null primary key auto_increment,
a_date date not null);

insert into my_dates (a_date) values ('2012-01-01');
insert into my_dates (a_date) values ('2012-02-01');
insert into my_dates (a_date) values ('2012-04-01');
insert into my_dates (a_date) values ('2012-05-01');
insert into my_dates (a_date) values ('2012-07-01');
insert into my_dates (a_date) values ('2012-08-01');
insert into my_dates (a_date) values ('2012-09-01');
insert into my_dates (a_date) values ('2012-10-01');
insert into my_dates (a_date) values ('2012-11-01');
insert into my_dates (a_date) values ('2012-12-01');

-- Get me the dates in all_dates that are not present in my_dates 
-- i.e. missing dates
select ad.a_date
from all_dates ad
left join my_dates md on ad.a_date = md.a_date
where md.a_date is null;