List all Dates between two Date columns of a table

Question

My table PRODUCT has 3 columns:

Product_ID
INTRODUCED_DATE
WITHDRAWAL_DATE

I need to create a derived table PRODUCT_ALL_DATES from this table that list all the dates that a Product was active.The Date ranges are INTRODUCED_DATE (Start Date) and WITHDRAWAL_DATE (End Date)

How can I achieve this in SQL Server?I have indicated the sample output in the attached image:

Thanks!

Answer 1

declare @dateh table(ind int identity(1,1),date1 smalldatetime,date2 smalldatetime)

insert into @dateh select '1/1/2011','1/15/2011'

select * from @dateh

;with T as
(
    select date1,date2 from @dateh as d
    union all
    select dateadd(dd,1,date1),date2 From T 
    where  dateadd(dd,1,date1)<= date2
)
Select date1 from T