Search code examples
sqldatedate-rangeauto-generate

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


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