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!

possible duplicate of [Get a list of dates between two dates](http://stackoverflow.com/questions/510012/get-a-list-of-dates-between-two-dates) — Andriy M, Oct 19 '11 at 08:45
Also worth checking out: [How to display all the dates between two given dates in SQL](http://stackoverflow.com/questions/1490566/how-to-display-all-the-dates-between-two-given-dates-in-sql) — Andriy M, Oct 19 '11 at 08:46

score 6 · Answer 1 · answered Oct 19 '11 at 04:03

6

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

answered Oct 19 '11 at 04:03

rahularyansharma

11,156
18
79
135

1

If the recursion executes more that 100 times (larger date ranges), it will terminate. You will need to add OPTION (MAXRECURSION 0) at the end for it to execute properly on bigger date ranges. – Nick De Beer Oct 19 '15 at 23:00

score 1 · Answer 2 · answered Oct 19 '11 at 04:06

I can think of 2 ways to achieve this

Write a stored procedure and loop throuh to populate the second table
Write a program in another language to do it.

The obvious question is why do you want to do this? what is the problem that cannot be solved with data being in its current form

score 1 · Answer 3 · answered Oct 20 '11 at 01:01

Here is the answer of your query, i tried this by using Cursor. It is working fine.

    CREATE TABLE product
(
product_id int
,INTRODUCED_DATE DATETIME
,WITHDRAWAL_DATE DATETIME
)

INSERT INTO product VALUES (100,'01-01-2011','01-05-2011')
INSERT INTO product VALUES (200,'05-30-2011','06-05-2011')

CREATE TABLE PRODUCT_ALL_DATES
(
product_id int
,Dates_Active DATETIME
)

DECLARE @product int
,@Introduct_Date DATETIME
,@Withdrawal_date DATETIME
,@Dates_Active DATETIME

DECLARE pointer_cur CURSOR FAST_FORWARD
FOR
SELECT * FROM product a

OPEN pointer_cur

FETCH NEXT FROM pointer_cur
INTO @Product,@Introduct_Date,@Withdrawal_date

WHILE(@@FETCH_STATUS=0)
BEGIN

    WHILE(@Introduct_Date<=@Withdrawal_date)
    BEGIN
        SET @Dates_Active=@Introduct_Date

        INSERT INTO PRODUCT_ALL_DATES
        SELECT @product,@Dates_Active

        SELECT @Introduct_Date=dateadd(day,1,@Introduct_Date)

    END

FETCH NEXT FROM POINTER_CUR
INTO @Product,@Introduct_Date,@Withdrawal_date

END

CLOSE POINTER_CUR
DEALLOCATE POINTER_CUR

SELECT * FROM PRODUCT_ALL_DATES

score 0 · Answer 4 · answered Oct 19 '11 at 04:10

Depending on the total ranges of dates, this should work. You might need to adjust the MAX recursion if you have large range of dates overall.

;WITH DaysCTE( Date ) as
(
    SELECT MIN(INTRODUCED_DATE) AS Date FROM PRODUCT
        UNION ALL
    SELECT DATEADD(day, 1, Date) 
        FROM DaysCTE
        where Date < (SELECT MAX(@WITHDRAWAL_DATE) FROM PRODUCT)
)

SELECT
   PRODUCT_ID,
   DaysCTE.Date
FROM
   PRODUCT
INNER JOIN DaysCTE
 ON DaysCTE.Date >= PRODUCT.INTRODUCED_DATE
    AND DaysCTE.DATE <= PRODUCT.WITHDRAWAL_DATE

List all Dates between two Date columns of a table

4 Answers4