7

Is there a way to get all dates between two dates without using any MySQL table

Something like: 

SELECT date BETWEEN '2012-02-10' AND '2012-02-15'

that would result in this:

out put date list
2012-02-10
...
2012-02-15
jpw
  • 44,361
  • 6
  • 66
  • 86
Swarna Sekhar Dhar
  • 550
  • 1
  • 8
  • 25
  • A loop in a procedure would do that – juergen d Nov 17 '14 at 09:10
  • 1
    possible duplicate of [Get a list of dates between two dates](http://stackoverflow.com/questions/510012/get-a-list-of-dates-between-two-dates) – jpw Nov 17 '14 at 09:16
  • Check this answer I gave recently I saved dated in a tabe, you can just use the select part http://stackoverflow.com/questions/26944750/create-a-temporary-table-with-dates/26944861#26944861 – Abhik Chakraborty Nov 17 '14 at 09:18

1 Answers1

23

Here is the query:
This query gives proper result in both the databases : MariaDB & MySQL.

SELECT ADDDATE('2012-02-10', INTERVAL @i:=@i+1 DAY) AS DAY
FROM (
SELECT a.a
FROM (SELECT 0 AS a 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) AS a
CROSS JOIN (SELECT 0 AS a 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) AS b
CROSS JOIN (SELECT 0 AS a 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) AS c
) a
JOIN (SELECT @i := -1) r1
WHERE 
@i < DATEDIFF('2012-02-15', '2012-02-10')
Kruti Patel
  • 1,422
  • 2
  • 23
  • 36
  • hi @kruti-patel, thank you for this query. I created a store procedure from it with ease, I would kindly ask you to explain the logic behind this query. Thank you in advance. – Matija Feb 23 '17 at 14:11
  • 1
    hi @MatijaBaric, From clause will create combination values and select clause will start from startdate and will increment date by 1 everytime till the difference between two dates is not achieved. – Kruti Patel Feb 27 '17 at 09:39