Get Start and End Date from a Given Date Range

Jes Sie

I need to save to a table the fields "startDate", "endDate", and "dateAlias" from a given date range. Saving should be by 30 days. So, let's say the date ranges from January 20, 2020 to march 20, 2021. I need to save to my table a period before the start date starts to the date end like this:

startDate	endDate	dateAlias
2000/01/01	2020/01/19	Month00
2020/01/20	2020/02/19	Month01
2020/02/20	2020/03/19	Month02
2020/03/20	2020/04/19	Month03

and so on.

the start date of the first row is static. I hope someone can help me. Thanks in advance.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Gupta6y ago
Result is different because you are inserting the enddate which is fixed at the time of declaration.

Made some changes, hope it suits your requirement, change it accordingly
declare @startdate date= '2020/01/20',
        @enddate   date= '2020/03/20',
        @resultDate date,
        @Alias int= 0,
        @DateAlias nvarchar(50);

set @resultDate = DATEADD(DAY, 30, @startDate);
while(@startdate <=@enddate)
begin
    set @alias = @alias+1;
    set @DateAlias =CONCAT('Month', @alias);
    insert into dbo.tblGetDateRange (dateStart, dateEnd, dateAlias)values
                                (@startdate, @resultDate, @DateAlias)

    set @startdate = DATEADD(DAY, 30, @startDate);
    set @resultDate = DATEADD(DAY, 30, @startDate);

end;
select * from tblGetDateRange
Output
dateStart           dateEnd        dateAlias
2020-01-20 2020-02-19 Month1
2020-02-19          2020-03-20      Month2
2020-03-20       2020-04-19 Month3
+1
Jes Sie6y ago
Thanks for your reply Amit. I tried it using this query below but it gives me a different result.

And how will I do the alias part?
0
Amit Gupta6y ago
If I understood correctly, you want to fetch the data within a days range of 30. If it is so then 1. Get the start date. 2. Add 30 days like DATEADD(DAY, 30, '2020/9/21'). 3. You got the enddate from step 2 now 4. Select * from tablename where date between @startdate and @enddate
0