MySQL: Select All Dates In a Range Even If No Records Present

by

I hope you will figure out the rest.

select  * from (
select date_add('2003-01-01 00:00:00.000', INTERVAL n5.num*10000+n4.num*1000+n3.num*100+n2.num*10+n1.num DAY ) as date from
(select 0 as num
   union all select 1
   union all select 2
   union all select 3
   union all select 4
   union all select 5
   union all select 6
   union all select 7
   union all select 8
   union all select 9) n1,
(select 0 as num
   union all select 1
   union all select 2
   union all select 3
   union all select 4
   union all select 5
   union all select 6
   union all select 7
   union all select 8
   union all select 9) n2,
(select 0 as num
   union all select 1
   union all select 2
   union all select 3
   union all select 4
   union all select 5
   union all select 6
   union all select 7
   union all select 8
   union all select 9) n3,
(select 0 as num
   union all select 1
   union all select 2
   union all select 3
   union all select 4
   union all select 5
   union all select 6
   union all select 7
   union all select 8
   union all select 9) n4,
(select 0 as num
   union all select 1
   union all select 2
   union all select 3
   union all select 4
   union all select 5
   union all select 6
   union all select 7
   union all select 8
   union all select 9) n5
) a
where date >'2011-01-02 00:00:00.000' and date < NOW()
order by date

With 

select n3.num*100+n2.num*10+n1.num as date

you will get a column with numbers from 0 to max(n3)*100+max(n2)*10+max(n1)

Since here we have max n3 as 3, SELECT will return 399, plus 0 -> 400 records (dates in calendar).

You can tune your dynamic calendar by limiting it, for example, from min(date) you have to now().

More Related Contents:

  1. Get a list of dates between two dates
  2. MySQL: Find Missing Dates Between a Date Range
  3. MySQL how to fill missing dates in range?
  4. Left Outer Join doesn’t return all rows from my left table?
  5. How to populate a table with a range of dates?
  6. MySQL Query GROUP BY day / month / year
  7. Error in MySQL when setting default value for DATE or DATETIME
  8. How to get the number of days of difference between two dates on MySQL?
  9. Mysql: Select all data between two dates
  10. How to find gaps in sequential numbering in mysql?
  11. Difference between two dates in MySQL
  12. How to get difference between two dates in months using MySQL query?
  13. Select mysql query between date?
  14. How do I select between the 1st day of the current month and current day in MySQL?
  15. How to get an age from a D.O.B field in MySQL?
  16. mysql: get record count between two date-time
  17. Check for x consecutive days – given timestamps in database
  18. How to get first day of every corresponding month in mysql?
  19. How to cast DATETIME as a DATE in mysql?
  20. MySQL: How to select records for this week?
  21. Compare only day and month with date field in mysql
  22. MySQL – select data from database between two dates
  23. How to select only date from a DATETIME field in MySQL?
  24. Select all months within given date span, including the ones with 0 values
  25. Update date + one year in mysql
  26. Insert/ Update random date in MySQL
  27. mysql date comparison with date_format
  28. How to compare timestamp dates with date-only parameter in MySQL?
  29. SQL date format convert? [dd.mm.yy to YYYY-MM-DD]
  30. How do I extract Month and Year in a MySQL date and compare them?

Leave a Comment