CODEWITHSUNDEEP
sql-server
D.Alex
D.Alex

Reputation: 39

Split date range into day wise rows in sql

I have a table which have 3 columns

enter image description here

An I want to split on each day based on Promo_Period_Start and Promo_Period_End

What I tried is the following

declare @d datetime;
set @d = getdate();

select *
from   [dbo].[T]
where  @d between Promo_Period_Start and Promo_Period_End

SAMPLE DATA

enter image description here

Upvotes: 1

Views: 5921

Answers (1)

Daniel Brughera
Daniel Brughera

Reputation: 1651

Use a recursive CTE

;WITH cte AS (
    SELECT ID, Promo_Period_Start,Promo_Period_End
    FROM T
    UNION ALL
    SELECT ID, DATEADD(day, 1, Promo_Period_Start), Promo_Period_End
    FROM cte
    WHERE Promo_Period_Start < Promo_Period_End
)
SELECT * FROM cte

Upvotes: 1

Related Questions