Hi, 

i have a table like below

CREATE TABLE #Test (FromDate DATE,ToDate DATE)
insert into #Test VALUES ('2015-08-08','2015-08-11')
insert into #Test VALUES ('2015-08-13','2015-08-16')
insert into #Test VALUES ('2015-08-19','2015-08-21')
SELECT * from #Test
drop TABLE #Test

i need to display the dates as single column between from and todate.

my expected result is like below

CREATE TABLE #Result (ResDate DATE)
insert into #Result VALUES ('2015-08-08')
insert into #Result VALUES ('2015-08-09')
insert into #Result VALUES ('2015-08-10')
insert into #Result VALUES ('2015-08-11')
insert into #Result VALUES ('2015-08-13')
insert into #Result VALUES ('2015-08-14')
insert into #Result VALUES ('2015-08-15')
insert into #Result VALUES ('2015-08-16')
insert into #Result VALUES ('2015-08-19')
insert into #Result VALUES ('2015-08-20')
insert into #Result VALUES ('2015-08-21')

SELECT * from #Result
drop TABLE #Result

Thanks

You want a calendar table to handle problems like this.  The best way is to create a permanent calendar table and use it.  Calendar tables have many uses, google "SQL Calendar table" to see many pages on how to create one and use it..  If you had a calendar table named dbo.Calendar with the column containing the date named dt, then your query would be just

Select Distinct c.dt As ResDate
From dbo.Calendar c
Inner Join #Test t On c.dt Between t.FromDate And t.ToDate
Order By c.dt;

If you don't have a calendar table and for some reason don't want one, you can do this with a recursive cte, that would be

;With cteMinMax As
(Select Min(FromDate) As FromDate, Max(ToDate) As ToDate
From #Test),
cteCalendar As
(Select FromDate As dt
From cteMinMax
Union All
Select DateAdd(day, 1, c.dt) As dt
From cteCalendar c
Inner Join cteMinMax m On c.dt < m.ToDate)
Select Distinct c.dt As ResDate
From cteCalendar c
Inner Join #Test t On c.dt Between t.FromDate And t.ToDate
Order By c.dt;

Tom

Thank you Tom

You want a calendar table to handle problems like this.  The best way is to create a permanent calendar table and use it.  Calendar tables have many uses, google "SQL Calendar table" to see many pages on how to create one and use it..  If you had a calendar table named dbo.Calendar with the column containing the date named dt, then your query would be just

Select Distinct c.dt As ResDate
From dbo.Calendar c
Inner Join #Test t On c.dt Between t.FromDate And t.ToDate
Order By c.dt;

If you don't have a calendar table and for some reason don't want one, you can do this with a recursive cte, that would be

;With cteMinMax As
(Select Min(FromDate) As FromDate, Max(ToDate) As ToDate
From #Test),
cteCalendar As
(Select FromDate As dt
From cteMinMax
Union All
Select DateAdd(day, 1, c.dt) As dt
From cteCalendar c
Inner Join cteMinMax m On c.dt < m.ToDate)
Select Distinct c.dt As ResDate
From cteCalendar c
Inner Join #Test t On c.dt Between t.FromDate And t.ToDate
Order By c.dt;

Tom

• Marked as answer by Thangam - Kasim 23 hours 55 minutes ago