CODEWITHSUNDEEP
sql-server
Tiago Muller Pereira
Tiago Muller Pereira

Reputation: 23

Get week periods starting to sunday to saturday in SQL Server

I need to get a list of weeks for an specific month and year. Showing the initial and final date for each week.

It must starts on Sunday, and end on Saturday.

Example: for month 10, year 2018:

Result

Note that the end date may exceed the month because you should consider that the start date is part of the selected month.

Upvotes: 0

Views: 650

Answers (2)

Squirrel
Squirrel

Reputation: 24783

declare @year   int, 
        @month  int

select  @year   = 2018,
        @month  = 10

; with 
-- get first and last day of the month
dates as
(
    select  first_of_mth = dateadd(year, @year - 1900, dateadd(month, @month - 1, 0)),
            last_of_mth = dateadd(year, @year - 1900, dateadd(month, @month, -1))
),
-- get first sunday of the month
dates2 as
(
    select  first_of_mth, last_of_mth,
            first_sun_of_mth = dateadd(week, datediff(week, 0, dateadd(day, 7 - datepart(day, first_of_mth), first_of_mth)), -1)
    from    dates
),
-- recursive CTE to get all weeks of the month
weeks as
(
    select  week_no     = 1, 
            begin_date  = first_sun_of_mth,
            end_date    = dateadd(day, 6, first_sun_of_mth),
            last_of_mth
    from    dates2

    union all

    select  week_no     = week_no + 1,
            begin_date  = dateadd(day, 7, begin_date),
            end_date    = dateadd(day, 7, end_date),
            last_of_mth
    from    weeks
    where   dateadd(day, 7, begin_date) <= last_of_mth
)
select  week_no, begin_date, end_date
from    weeks

Upvotes: 0

TomC
TomC

Reputation: 2814

This query will work. 

;with w as (
select convert(date,'2018-10-01') as startdate, convert(date,'2018-10-01') as dt
union all
select startdate, dateadd(d,1,dt) from w where month(startdate)=month(dateadd(d,1,dt))
) 
select row_number() over (order by dt) as wk, 
    dt as wkstart, 
    dateadd(d,6,dt) as wkend 
from w 
where datediff(d,convert(date,'2018-10-07'),dt)%7=0

Result:

wk  wkstart     wkend
1   2018-10-07  2018-10-13
2   2018-10-14  2018-10-20
3   2018-10-21  2018-10-27
4   2018-10-28  2018-11-03

EDIT: I changed the way that sundays are found to be language agnostic.

Upvotes: 1

Related Questions