How to Auto generate dates between date range using SQL Query?

Issue

I just want to generate the date between data range using SQL Query.

Source:

enter image description here

Result:

enter image description here

Thanks,
Lawrance A

Solution

Here is how to accomplish this by using a tally table to create a calendar table:

declare @source table
(
    user_id int not null primary key clustered,
    from_date date not null,
    to_date date not null
);

insert into @source
values
(1, '02/20/2019', '02/23/2019'),
(2, '02/22/2019', '02/28/2019'),
(3, '03/01/2019', '03/05/2019');

with
rows as
(
    select top 1000
    n = 1
    from sys.messages
),
tally as
(
    select n = row_number() over(order by (select null)) - 1
    from rows
),
calendar as
(
    select
    date = dateadd(dd, n, (select min(from_date) from @source))
    from tally
)
select
s.user_id,
c.date
from @source s
cross join calendar c
where c.date between s.from_date and s.to_date;

Result set:
enter image description here

Answered By – knot22

Answer Checked By – Cary Denson (AngularFixing Admin)

Leave a Reply

Your email address will not be published.