get missing dates between two range of date

Question

I want to get missings dates in between my given range of date for example

date between 2019-02-01 and 2019-01-28

i have only 5 days between these range. but i want to get all date with null value if not exist like this

1,"abc","2019-02-01",
2,"test","2019-2-06",

if not exist then show like this

    3,"null","2019,02-02"  4,"null","2019,02-03" 5,"null","2019,02-03",....
,
,

Create a help calendar table (or cte), having all dates of interest. OUTER JOIN that table. — jarlh, Feb 06 '19 at 20:04
And if you're still struggling, see [Why should I provide an MCVE for what seems to me to be a very simple SQL query](http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Feb 06 '19 at 20:42
You should look [this](https://stackoverflow.com/questions/20687787/how-to-generate-date-in-sql-query) , this is solution with function. — MladenB, Feb 06 '19 at 21:11
Possible duplicate of [MySQL how to fill missing dates in range?](https://stackoverflow.com/questions/3538858/mysql-how-to-fill-missing-dates-in-range) — Nick, Feb 06 '19 at 22:39
Are you sure it is a true NULL and not a blank? If you are not sure which it is you can try the **if date is Null or if date is ''** - The two single quotes will give you fields with no data, but are not null. — Ouida, Feb 06 '19 at 20:48

score 0 · Answer 1 · answered Feb 06 '19 at 21:05

In order to return those "missing" date values in a resultset, we need a rowsource that provides them. That is, those values have to come from somewhere; rows don't just spontaneously appear in a query from nowhere.

One solution is to add a "calendar" table that contains the set of date values.

CREATE TABLE `cal` ( `dt` date NOT NULL, PRIMARY KEY (`dt`)) ;

INSERT INTO `cal` (`dt`) VALUES ('2019-02-01'), ('2019-02-02'), ('2019-02-03')
                              , ('2019-02-04'), ('2019-02-05'), ('2019-02-06')
                              , ...

Then we can reference the table in a query. We can use an outer join to get matching rows. Where there isn't a matching row, we still get back the date from the calendar table

 SELECT c.dt      AS dt
      , t.mycol   AS mycol 
   FROM cal c
   LEFT
   JOIN mytable t
     ON t.dt = c.dt 
  WHERE c.dt >= '2019-02-01'
    AND c.dt <  '2019-02-07'
  ORDER
     BY c.dt

We don't necessarily need to create a calendar table. Another possibility is to generate the set of dates in an inline view within the query.

But the principle is the same. We need a rowsource that will return the date values when we reference it in the FROMclause of a query.

get missing dates between two range of date

1 Answers1