How to display all the dates between two dates stored in my table by single query?

Question

I want to display all the dates between two dates for a particular record

And this is the table :

ID Start_Date  End_Date
-------------------------
1  2013-01-14  2013-01-18
2  2013-02-01  2013-02-04

and now i want to get all the dates between from date and to date.

Expected output

ID Date
-------------
1  2013-01-14
1  2013-01-15
1  2013-01-16
1  2013-01-17
1  2013-01-18
2  2013-02-01
2  2013-02-02
2  2013-02-03
2  2013-02-04

any help will be appreciated

I'd use a calendar table (or cte) storing all needed dates. OUTER JOIN that table. — jarlh, Jan 29 '19 at 08:54
Try to use https://stackoverflow.com/questions/6870499/generate-series-equivalent-in-mysql — Radim Bača, Jan 29 '19 at 08:56
Try [generate-days-from-date-range](https://stackoverflow.com/questions/2157282/generate-days-from-date-range) — JIJOMON K.A, Jan 29 '19 at 09:05
Which MySQL are you using? A recursive cte can create those dates! — jarlh, Jan 29 '19 at 09:08
Too bad, you need a newer version to get cte support. Now you really need that calendar table... — jarlh, Jan 29 '19 at 09:11
Consider handling issues of data display in application code — Strawberry, Jan 29 '19 at 09:14

score 0 · Answer 1 · answered Jan 29 '19 at 09:23

EDIT:: this works on sql server (my bad didn't see the tag). Will probably do for mysql as well as it supports the same date functions

try something like this replacing my hard coded values with your table values. This brings back all days between 2 dates including the start date. If you wish to keep the max date too, you'll have to modify my code

create table #datediff
(
id int,
dates date
)

declare @min date, @max date,@df int

set @min = '2018-01-30'
set @max = '2018-02-5'
set @df = DATEDIFF(DAY,@min,@max)


while @df <> 0
begin
    insert into #datediff
    Values ( 1, DATEADD(D,-1,@max))


    set @df = @df -1
    set @max = DATEADD(D,-1,@max)       
end


select * from #datediff

drop table #datediff

it is a temporary table only used to get the results you need (hashtag is for temporary). It does not mess your database's structure — Bonzay, Jan 29 '19 at 09:29
yes i know but it will get all dated between two dates i want all dates between from date and to date column — , Jan 29 '19 at 09:31

How to display all the dates between two dates stored in my table by single query?

1 Answers1