How to generate weekwise dates between two dates

Siva Dss 0
Viewed 25

I want to generate list of weekwise dates between start date and end date taking monday as start of the week and sunday as end of the week. If month end comes then it should end with date and for the next month it should start new record. I tried writing using Datefirst but it was not working as set operator was not working in function. For Example StartDate = '04-08-2022' (DD-MM-YYYY), End Date = '08-09-2022'
The desired output as below

04-08-2022     08-08-2022
09-08-2022     15-08-2022
16-08-2022     22-08-2022
23-08-2022     29-08-2022
30-08-2022     31-08-2022
01-09-2022     04-09-2022
05-09-2022     08-09-2022
sql-server-2008 sql-server
1 Answers

Having a calendar table in your database has many uses, and this is one of them.

Otherwise, some SQL systems provide a GENERATOR to do this, but SQL Server does not.

With SQL Server, you may have to write a recursive CTE. This is not tested since I don't have SQL Server, but perhaps something like this:

Declare  @FromDate    Date = '2022-04-08',
         @ToDate      Date = '2022-08-09'

;With DateCte (Date) As
(
    Select  @FromDate Union All
    Select  DateAdd(Week, 1, Date)
    From    DateCte
    Where   Date < @ToDate
)
Select  Date
From    DateCte
Option  (MaxRecursion 0)

Source: https://riptutorial.com/sql-server/example/11098/generating-date-range-with-recursive-cte

userZth90
Related