Get Start and End Date from a Given Date Range

Question

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.

Amit Gupta · Answer

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 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

Jes Sie · Answer

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?

Amit Gupta · Answer

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