Mysql: Select all data between two dates

by

You can use a concept that is frequently referred to as ‘calendar tables’. Here is a good guide on how to create calendar tables in MySql:

-- create some infrastructure
CREATE TABLE ints (i INTEGER);
INSERT INTO ints VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

-- only works for 100 days, add more ints joins for more
SELECT cal.date, tbl.data
FROM (
    SELECT '2009-06-25' + INTERVAL a.i * 10 + b.i DAY as date
    FROM ints a JOIN ints b
    ORDER BY a.i * 10 + b.i
) cal LEFT JOIN tbl ON cal.date = tbl.date
WHERE cal.date BETWEEN '2009-06-25' AND '2009-07-01';

You might want to create table cal instead of the subselect.

More Related Contents:

  1. Select all months within given date span, including the ones with 0 values
  2. Left Outer Join doesn’t return all rows from my left table?
  3. Get a list of dates between two dates
  4. How to convert a string to date in MySQL?
  5. Error in MySQL when setting default value for DATE or DATETIME
  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 Dates In a Range Even If No Records Present
  9. Difference between two dates in MySQL
  10. CURRENT_DATE/CURDATE() not working as default DATE value
  11. How do I select between the 1st day of the current month and current day in MySQL?
  12. Get the latest date from grouped MySQL data
  13. How to get an age from a D.O.B field in MySQL?
  14. mysql: get record count between two date-time
  15. How to get first day of every corresponding month in mysql?
  16. Count days between two dates, excluding weekends (MySQL only)
  17. How to select the most recent set of dated records from a mysql table
  18. How do I get the first day of the week of a date in mysql?
  19. MySQL date format
  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. Insert/ Update random date in MySQL
  25. How to compare timestamp dates with date-only parameter in MySQL?
  26. #1292 – Incorrect date value: ‘0000-00-00’ [duplicate]
  27. MySQL cumulative sum grouped by date
  28. SQL date format convert? [dd.mm.yy to YYYY-MM-DD]
  29. What’s mysql’s “BETWEEN” performance over..?
  30. Working with INTERVAL and CURDATE in MySQL

Leave a Comment