MySQL: Find Missing Dates Between a Date Range

by

This is a second answer, I’ll post it separately.

SELECT DATE(r1.reportdate) + INTERVAL 1 DAY AS missing_date
FROM Reports r1
LEFT OUTER JOIN Reports r2 ON DATE(r1.reportdate) = DATE(r2.reportdate) - INTERVAL 1 DAY
WHERE r1.reportdate BETWEEN '2011-01-01' AND '2011-04-30' AND r2.reportdate IS NULL;

This is a self-join that reports a date such that no row exists with the date following.

This will find the first day in a gap, but if there are runs of multiple days missing it won’t report all the dates in the gap.

More Related Contents:

  1. Get a list of dates between two dates
  2. MySQL: Select All Dates In a Range Even If No Records Present
  3. How to populate a table with a range of dates?
  4. How to convert a string to date in MySQL?
  5. MySQL Query GROUP BY day / month / year
  6. How to get the number of days of difference between two dates on MySQL?
  7. Converting a date in MySQL from string field
  8. Mysql: Select all data between two dates
  9. MySQL SELECT WHERE datetime matches day (and not necessarily time)
  10. MySQL Query – Records between Today and Last 30 Days
  11. How to find gaps in sequential numbering in mysql?
  12. CURRENT_DATE/CURDATE() not working as default DATE value
  13. How to get difference between two dates in months using MySQL query?
  14. Select mysql query between date?
  15. How to select date from datetime column?
  16. Get the latest date from grouped MySQL data
  17. mysql: get record count between two date-time
  18. Check for x consecutive days – given timestamps in database
  19. How to get first day of every corresponding month in mysql?
  20. Count days between two dates, excluding weekends (MySQL only)
  21. How to select the most recent set of dated records from a mysql table
  22. How to cast DATETIME as a DATE in mysql?
  23. How do I get the first day of the week of a date in mysql?
  24. MySQL date format
  25. Select all months within given date span, including the ones with 0 values
  26. Update date + one year in mysql
  27. mysql date comparison with date_format
  28. #1292 – Incorrect date value: ‘0000-00-00’ [duplicate]
  29. MySQL cumulative sum grouped by date
  30. How do I extract Month and Year in a MySQL date and compare them?

Leave a Comment