I need a solution that takes the starting date and ending date as input and provides me with a list of week numbers between those dates. For instance, if I input 01/11/2019 as the starting date and 14/12/2019 as the ending date, the output will be:
1
2
3
4
5
1
2
3
(the first 5 weeks are for November and the next 3 weeks are for December)..... Now let's look at another example: If I provide the starting date as 14/11/2019 and the ending date as 14/12/2019, the output will be:
3
4
5
1
2
3
(the first 3 weeks are for November and the next 3 weeks are for December) ....
DECLARE @StartDate AS DATETIME
DECLARE @EndDate AS DATETIME
DECLARE @CurrentDate AS DATETIME
SET @StartDate = '2019-11-01'
SET @EndDate = '2019-12-14'
SET @CurrentDate = @StartDate
WHILE (@CurrentDate < @EndDate)
BEGIN
Print datepart(day, datediff(day, 0, @CurrentDate)/7 * 7)/7 + 1
SET @CurrentDate = DATEADD(DAY, 7, @CurrentDate);
END