Get start and end date for each week in month

Question

I need to get start and end date for each week in given month/year. (month and year are always given - like march 2017).

Example, january 2017:

1 week : '2017-01-01' - '2017-01-01'
2 week:  '2017-01-02' - '2017-01-08'
3 week:  '2017-01-09' - '2017-01-15'
4 week:  '2017-01-16' - '2017-01-22'
5 week:  '2017-01-23' - '2017-01-29'
6 week:  '2017-01-30' - '2017-01-31'

I already know how to get number of weeks for given month / year:

select *,
    DATEDIFF(WEEK, DATEADD(day,-1,StartAt), DATEADD(day,-1,EndAt)) +1
    as NumWeeks

But how to get start / end date for each week for the given month/year?

As Panagiotis Kanavos commented on your previous question you quoted, a calendar table would very much facilitate all this stuff. So perhaps some redesign is in order. — Giorgos Altanis, Mar 14 '17 at 19:32

score 3 · Accepted Answer · answered Mar 14 '17 at 21:48

If you just want a function to return the weeks of a month for one month then this would do what you want:

create function dbo.udf_weeks_of_month (@fromdate date) 
returns table as return (
with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, dates as (
  select top (datediff(day, @fromdate, dateadd(month, datediff(month, 0, @fromdate )+1, 0))) 
    [DateValue]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate))
  from n as deka cross join n as hecto
)
select 
    WeekOfMonth = row_number() over (order by datepart(week,DateValue))
  , Week        = datepart(week,DateValue)
  , WeekStart   = min(DateValue)
  , WeekEnd     = max(DateValue)
from dates
group by datepart(week,DateValue)
);

and calling it like so:

set datefirst 1;
select * from dbo.udf_weeks_of_month('20170101');

returns:

+-------------+------+------------+------------+
| WeekOfMonth | Week | WeekStart  |  WeekEnd   |
+-------------+------+------------+------------+
|           1 |    1 | 2017-01-01 | 2017-01-01 |
|           2 |    2 | 2017-01-02 | 2017-01-08 |
|           3 |    3 | 2017-01-09 | 2017-01-15 |
|           4 |    4 | 2017-01-16 | 2017-01-22 |
|           5 |    5 | 2017-01-23 | 2017-01-29 |
|           6 |    6 | 2017-01-30 | 2017-01-31 |
+-------------+------+------------+------------+

and this call:

select * from dbo.udf_weeks_of_month('february 2017');

returns:

+-------------+------+------------+------------+
| WeekOfMonth | Week | WeekStart  |  WeekEnd   |
+-------------+------+------------+------------+
|           1 |    6 | 2017-02-01 | 2017-02-05 |
|           2 |    7 | 2017-02-06 | 2017-02-12 |
|           3 |    8 | 2017-02-13 | 2017-02-19 |
|           4 |    9 | 2017-02-20 | 2017-02-26 |
|           5 |   10 | 2017-02-27 | 2017-02-28 |
+-------------+------+------------+------------+

rextester demo: http://rextester.com/VKPQU7936 (note: rextester reformats the dates)

Great answer @Sqlzimm, I have tried this way, works !! but what if my week starts from the past month and ends in this month.. can you help with this scenario, should I send question link ? — Dave, Jun 16 '21 at 06:54
@Dave I'd need more detail to understand your question and expected results. It sounds like you should post a question if you can't find an existing question/answer that match your requirements. — SqlZim, Jun 16 '21 at 16:02

SqlZim · Answer 2 · 2017-04-03T15:09:26.177

select 
    DateValue
  , WeekStart =convert(date,(
    case when datepart(week,DateValue) =1 
      then convert(date, (datename(year,DateValue) +'0101')) 
      else dateadd(day,@@datefirst-datepart(weekday,DateValue)-(@@datefirst-1),DateValue)
      end) ) 
  , WeekEnd  =convert(date,(
    case when datepart(week,DateValue) =53 
      then convert(date, (datename(year,DateValue) +'1231')) 
      else dateadd(day,(@@datefirst)-datepart(weekday,DateValue)+(7-@@datefirst),DateValue)
      end) ) 
from dates

rextester demo: http://rextester.com/KYKS44588

test setup:

set datefirst 1;
declare @fromdate date = '20161227', @thrudate date = '20201231';
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, dates as (
  select top (datediff(day, @fromdate, @thrudate)+1) 
    [DateValue]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate))
  from n as deka cross join n as hecto cross join n as kilo cross join n as [tenK] 
  order by 1
)
, test as (
  select 
      convert(varchar(10),DateValue,120) as Date
    , WeekStart = convert(varchar(10),convert(date,(
        case when datepart(week,DateValue) =1 
          then convert(date, (datename(year,DateValue) +'0101')) 
        else dateadd(day,@@datefirst-datepart(WeekDay,DateValue)-(@@datefirst-1),DateValue)
        end) ),120)
    , WeekEnd  = convert(varchar(10),convert(date,(
        case when datepart(week,DateValue) =53 
          then convert(date, (datename(year,DateValue) +'1231')) 
        else dateadd(day,(@@datefirst)-datepart(WeekDay,DateValue)+(7-@@datefirst),DateValue)
        end) ),120)
    , week= datepart(week,DateValue)

  from dates
)
select *
  , dayname= datename(weekday,date) 
  , weekstartdayname= datename(weekday,weekstart) 
  , weekenddayname= datename(weekday,weekend) 
from test
where week > 51
   or week < 3
order by 1

results:

+------------+------------+------------+------+-----------+------------------+----------------+
|    Date    | WeekStart  |  WeekEnd   | week |  dayname  | weekstartdayname | weekenddayname |
+------------+------------+------------+------+-----------+------------------+----------------+
| 2016-12-27 | 2016-12-26 | 2016-12-31 |   53 | Tuesday   | Monday           | Saturday       |
| 2016-12-28 | 2016-12-26 | 2016-12-31 |   53 | Wednesday | Monday           | Saturday       |
| 2016-12-29 | 2016-12-26 | 2016-12-31 |   53 | Thursday  | Monday           | Saturday       |
| 2016-12-30 | 2016-12-26 | 2016-12-31 |   53 | Friday    | Monday           | Saturday       |
| 2016-12-31 | 2016-12-26 | 2016-12-31 |   53 | Saturday  | Monday           | Saturday       |
| 2017-01-01 | 2017-01-01 | 2017-01-01 |    1 | Sunday    | Sunday           | Sunday         |
| 2017-01-02 | 2017-01-02 | 2017-01-08 |    2 | Monday    | Monday           | Sunday         |
| 2017-01-03 | 2017-01-02 | 2017-01-08 |    2 | Tuesday   | Monday           | Sunday         |
| 2017-01-04 | 2017-01-02 | 2017-01-08 |    2 | Wednesday | Monday           | Sunday         |
| 2017-01-05 | 2017-01-02 | 2017-01-08 |    2 | Thursday  | Monday           | Sunday         |
| 2017-01-06 | 2017-01-02 | 2017-01-08 |    2 | Friday    | Monday           | Sunday         |
| 2017-01-07 | 2017-01-02 | 2017-01-08 |    2 | Saturday  | Monday           | Sunday         |
| 2017-01-08 | 2017-01-02 | 2017-01-08 |    2 | Sunday    | Monday           | Sunday         |
| 2017-12-18 | 2017-12-18 | 2017-12-24 |   52 | Monday    | Monday           | Sunday         |
| 2017-12-19 | 2017-12-18 | 2017-12-24 |   52 | Tuesday   | Monday           | Sunday         |
| 2017-12-20 | 2017-12-18 | 2017-12-24 |   52 | Wednesday | Monday           | Sunday         |
| 2017-12-21 | 2017-12-18 | 2017-12-24 |   52 | Thursday  | Monday           | Sunday         |
| 2017-12-22 | 2017-12-18 | 2017-12-24 |   52 | Friday    | Monday           | Sunday         |
| 2017-12-23 | 2017-12-18 | 2017-12-24 |   52 | Saturday  | Monday           | Sunday         |
| 2017-12-24 | 2017-12-18 | 2017-12-24 |   52 | Sunday    | Monday           | Sunday         |
| 2017-12-25 | 2017-12-25 | 2017-12-31 |   53 | Monday    | Monday           | Sunday         |
| 2017-12-26 | 2017-12-25 | 2017-12-31 |   53 | Tuesday   | Monday           | Sunday         |
| 2017-12-27 | 2017-12-25 | 2017-12-31 |   53 | Wednesday | Monday           | Sunday         |
| 2017-12-28 | 2017-12-25 | 2017-12-31 |   53 | Thursday  | Monday           | Sunday         |
| 2017-12-29 | 2017-12-25 | 2017-12-31 |   53 | Friday    | Monday           | Sunday         |
| 2017-12-30 | 2017-12-25 | 2017-12-31 |   53 | Saturday  | Monday           | Sunday         |
| 2017-12-31 | 2017-12-25 | 2017-12-31 |   53 | Sunday    | Monday           | Sunday         |
| 2018-01-01 | 2018-01-01 | 2018-01-07 |    1 | Monday    | Monday           | Sunday         |
| 2018-01-02 | 2018-01-01 | 2018-01-07 |    1 | Tuesday   | Monday           | Sunday         |
| 2018-01-03 | 2018-01-01 | 2018-01-07 |    1 | Wednesday | Monday           | Sunday         |
| 2018-01-04 | 2018-01-01 | 2018-01-07 |    1 | Thursday  | Monday           | Sunday         |
| 2018-01-05 | 2018-01-01 | 2018-01-07 |    1 | Friday    | Monday           | Sunday         |
| 2018-01-06 | 2018-01-01 | 2018-01-07 |    1 | Saturday  | Monday           | Sunday         |
| 2018-01-07 | 2018-01-01 | 2018-01-07 |    1 | Sunday    | Monday           | Sunday         |
| 2018-01-08 | 2018-01-08 | 2018-01-14 |    2 | Monday    | Monday           | Sunday         |
| 2018-01-09 | 2018-01-08 | 2018-01-14 |    2 | Tuesday   | Monday           | Sunday         |
| 2018-01-10 | 2018-01-08 | 2018-01-14 |    2 | Wednesday | Monday           | Sunday         |
| 2018-01-11 | 2018-01-08 | 2018-01-14 |    2 | Thursday  | Monday           | Sunday         |
| 2018-01-12 | 2018-01-08 | 2018-01-14 |    2 | Friday    | Monday           | Sunday         |
| 2018-01-13 | 2018-01-08 | 2018-01-14 |    2 | Saturday  | Monday           | Sunday         |
| 2018-01-14 | 2018-01-08 | 2018-01-14 |    2 | Sunday    | Monday           | Sunday         |
| 2018-12-24 | 2018-12-24 | 2018-12-30 |   52 | Monday    | Monday           | Sunday         |
| 2018-12-25 | 2018-12-24 | 2018-12-30 |   52 | Tuesday   | Monday           | Sunday         |
| 2018-12-26 | 2018-12-24 | 2018-12-30 |   52 | Wednesday | Monday           | Sunday         |
| 2018-12-27 | 2018-12-24 | 2018-12-30 |   52 | Thursday  | Monday           | Sunday         |
| 2018-12-28 | 2018-12-24 | 2018-12-30 |   52 | Friday    | Monday           | Sunday         |
| 2018-12-29 | 2018-12-24 | 2018-12-30 |   52 | Saturday  | Monday           | Sunday         |
| 2018-12-30 | 2018-12-24 | 2018-12-30 |   52 | Sunday    | Monday           | Sunday         |
| 2018-12-31 | 2018-12-31 | 2018-12-31 |   53 | Monday    | Monday           | Monday         |
| 2019-01-01 | 2019-01-01 | 2019-01-06 |    1 | Tuesday   | Tuesday          | Sunday         |
| 2019-01-02 | 2019-01-01 | 2019-01-06 |    1 | Wednesday | Tuesday          | Sunday         |
| 2019-01-03 | 2019-01-01 | 2019-01-06 |    1 | Thursday  | Tuesday          | Sunday         |
| 2019-01-04 | 2019-01-01 | 2019-01-06 |    1 | Friday    | Tuesday          | Sunday         |
| 2019-01-05 | 2019-01-01 | 2019-01-06 |    1 | Saturday  | Tuesday          | Sunday         |
| 2019-01-06 | 2019-01-01 | 2019-01-06 |    1 | Sunday    | Tuesday          | Sunday         |
| 2019-01-07 | 2019-01-07 | 2019-01-13 |    2 | Monday    | Monday           | Sunday         |
| 2019-01-08 | 2019-01-07 | 2019-01-13 |    2 | Tuesday   | Monday           | Sunday         |
| 2019-01-09 | 2019-01-07 | 2019-01-13 |    2 | Wednesday | Monday           | Sunday         |
| 2019-01-10 | 2019-01-07 | 2019-01-13 |    2 | Thursday  | Monday           | Sunday         |
| 2019-01-11 | 2019-01-07 | 2019-01-13 |    2 | Friday    | Monday           | Sunday         |
| 2019-01-12 | 2019-01-07 | 2019-01-13 |    2 | Saturday  | Monday           | Sunday         |
| 2019-01-13 | 2019-01-07 | 2019-01-13 |    2 | Sunday    | Monday           | Sunday         |
| 2019-12-23 | 2019-12-23 | 2019-12-29 |   52 | Monday    | Monday           | Sunday         |
| 2019-12-24 | 2019-12-23 | 2019-12-29 |   52 | Tuesday   | Monday           | Sunday         |
| 2019-12-25 | 2019-12-23 | 2019-12-29 |   52 | Wednesday | Monday           | Sunday         |
| 2019-12-26 | 2019-12-23 | 2019-12-29 |   52 | Thursday  | Monday           | Sunday         |
| 2019-12-27 | 2019-12-23 | 2019-12-29 |   52 | Friday    | Monday           | Sunday         |
| 2019-12-28 | 2019-12-23 | 2019-12-29 |   52 | Saturday  | Monday           | Sunday         |
| 2019-12-29 | 2019-12-23 | 2019-12-29 |   52 | Sunday    | Monday           | Sunday         |
| 2019-12-30 | 2019-12-30 | 2019-12-31 |   53 | Monday    | Monday           | Tuesday        |
| 2019-12-31 | 2019-12-30 | 2019-12-31 |   53 | Tuesday   | Monday           | Tuesday        |
| 2020-01-01 | 2020-01-01 | 2020-01-05 |    1 | Wednesday | Wednesday        | Sunday         |
| 2020-01-02 | 2020-01-01 | 2020-01-05 |    1 | Thursday  | Wednesday        | Sunday         |
| 2020-01-03 | 2020-01-01 | 2020-01-05 |    1 | Friday    | Wednesday        | Sunday         |
| 2020-01-04 | 2020-01-01 | 2020-01-05 |    1 | Saturday  | Wednesday        | Sunday         |
| 2020-01-05 | 2020-01-01 | 2020-01-05 |    1 | Sunday    | Wednesday        | Sunday         |
| 2020-01-06 | 2020-01-06 | 2020-01-12 |    2 | Monday    | Monday           | Sunday         |
| 2020-01-07 | 2020-01-06 | 2020-01-12 |    2 | Tuesday   | Monday           | Sunday         |
| 2020-01-08 | 2020-01-06 | 2020-01-12 |    2 | Wednesday | Monday           | Sunday         |
| 2020-01-09 | 2020-01-06 | 2020-01-12 |    2 | Thursday  | Monday           | Sunday         |
| 2020-01-10 | 2020-01-06 | 2020-01-12 |    2 | Friday    | Monday           | Sunday         |
| 2020-01-11 | 2020-01-06 | 2020-01-12 |    2 | Saturday  | Monday           | Sunday         |
| 2020-01-12 | 2020-01-06 | 2020-01-12 |    2 | Sunday    | Monday           | Sunday         |
| 2020-12-21 | 2020-12-21 | 2020-12-27 |   52 | Monday    | Monday           | Sunday         |
| 2020-12-22 | 2020-12-21 | 2020-12-27 |   52 | Tuesday   | Monday           | Sunday         |
| 2020-12-23 | 2020-12-21 | 2020-12-27 |   52 | Wednesday | Monday           | Sunday         |
| 2020-12-24 | 2020-12-21 | 2020-12-27 |   52 | Thursday  | Monday           | Sunday         |
| 2020-12-25 | 2020-12-21 | 2020-12-27 |   52 | Friday    | Monday           | Sunday         |
| 2020-12-26 | 2020-12-21 | 2020-12-27 |   52 | Saturday  | Monday           | Sunday         |
| 2020-12-27 | 2020-12-21 | 2020-12-27 |   52 | Sunday    | Monday           | Sunday         |
| 2020-12-28 | 2020-12-28 | 2020-12-31 |   53 | Monday    | Monday           | Thursday       |
| 2020-12-29 | 2020-12-28 | 2020-12-31 |   53 | Tuesday   | Monday           | Thursday       |
| 2020-12-30 | 2020-12-28 | 2020-12-31 |   53 | Wednesday | Monday           | Thursday       |
| 2020-12-31 | 2020-12-28 | 2020-12-31 |   53 | Thursday  | Monday           | Thursday       |
+------------+------------+------------+------+-----------+------------------+----------------+

Calendar and Numbers table references:

@SqlZim - can you please provide example values for DateValue and dates. Thank you. Something that can be run inside SSMS. — FrenkyB, Mar 14 '17 at 19:33
@JamesZ Good eye, `datename()` just avoids the need for explicit conversion to a char type. — SqlZim, Mar 14 '17 at 19:34
@FrenkyB I had a kink in my `@@datefirst` calculation. Corrected now. — SqlZim, Mar 14 '17 at 20:41

djangojazz · Answer 3 · 2017-03-14T20:23:18.450

1

Something like this?

DECLARE @Dates TABLE (DateId INT IDENTITY, Dt Date);

DECLARE @STart Date = DATEADD(Year, DATEDIFF(Year, 0, GETDATE()),0)

SET NOCOUNT ON;

WHILE @STart <= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
BEGIN
        INSERT INTO @Dates (Dt) VALUES (@STart)

    SELECT @Start = DATEADD(DAY, 1, @STart)
END

Updated

SELECT 
  DateId
, Dt
, DATEADD(WEEK, DATEDIFF(DAY, 0, Dt)/7, 0) AS WeekBeginningMondayOf
, DATEADD(DAY, 6, DATEADD(WEEK, DATEDIFF(DAY, 0, Dt)/7, 0)) AS WeekEndingSundayOf
, DENSE_RANK() OVER(PARTITION BY MONTH(Dt) ORDER BY DATEADD(WEEK, DATEDIFF(DAY, 0, Dt)/7, 0)) AS WeekInMonth
FROM @Dates

edited Mar 14 '17 at 20:23

answered Mar 14 '17 at 19:46

djangojazz

14,131
10
56
94

Is it possible to change this in a way that monday will be the first day in the week? – FrenkyB Mar 14 '17 at 19:57
Oops, I apologize. I forgot when you do the DateAdd DateDiff trick it does some weird things when using the straight up week for both. See updated. – djangojazz Mar 14 '17 at 20:23

score 1 · Answer 4 · edited May 23 '17 at 10:30

I think what you are looking for is here ->someone asked on stackoverflow is similar to what you are looking for it.

This is the final version, hope as per your expectations I build on the above link hope this is what you are looking for ( Apologies new to stackoverflow, unformatted t-sql below)

DECLARE @sDate DATETIME,
        @eDate DATETIME

SET @sDate = '2017-03-01'
SET @eDate = DATEADD(DAY,-1, CAST(Cast(DatePart(YEAR,@sdate) AS varchar(4)) 
+'-' + Cast((DatePart(MONTH,@sdate)+1) AS varchar(2))+ '-1' AS Date))
SET @sDate = CAST(Cast(DatePart(YEAR,@sdate) AS varchar(4)) +'-' 
+ Cast(DatePart(MONTH,@sdate) AS varchar(2))+ '-1' AS Date) 

DECLARE @startDayOfWeekOffSet int ;
SET @startDayOfWeekOffSet=
  (SELECT DATEPART(DW,@sDate));

DECLARE @DaysToGetFirstSaturday int
SET @DaysToGetFirstSaturday= 7- @startDayOfWeekOffSet 
DECLARE @firstEverStartOfWeekDay Date , @firstWeekEndDay Date
SET @firstEverStartOfWeekDay =@sDate;

IF @startDayOfWeekOffSet = 1 -- January
BEGIN
SET @firstEverStartOfWeekDay = @sDate
SET @DaysToGetFirstSaturday = 0
SET @firstWeekEndDay = @sDate END 
ELSE BEGIN IF DATEPART(MONTH,@sDate) > DATEPART(MONTH,@firstEverStartOfWeekDay)
AND @startDayOfWeekOffSet = 7 --FirstSundayNewMonth
BEGIN
SET @firstEverStartOfWeekDay =@sDate
SET @firstWeekEndDay = @sDate END ELSE --NotASundayNewMonth
BEGIN
SET @firstWeekEndDay = DATEADD(DAY,@DaysToGetFirstSaturday,@firstEverStartOfWeekDay);

END END
SELECT @firstWeekEndDay AS firstWeekEndDay,
       @firstEverStartOfWeekDay AS firstEverStartOfWeekDay,
       @DaysToGetFirstSaturday AS DaysToGetFirstSaturday,
       @startDayOfWeekOffSet AS startDayOfWeekOffSet ;

WITH cte AS
  (SELECT 1 AS WeekNum,
          @firstEverStartOfWeekDay StartDate,
          @firstWeekEndDay EndDate
   UNION ALL SELECT WeekNum + 1, --Case when @scenario = 'NewYear' Then
 dateadd(DAY, 1, cte.EndDate) --Else dateadd(ww, 1, cte.StartDate) End as
 StartDate,
 CASE
     WHEN StartDate = EndDate THEN DateAdd(DAY,-1,dateadd(DAY, 8, cte.EndDate))
     ELSE dateadd(ww, 1, cte.EndDate)
 END AS EndDate
   FROM cte
   WHERE dateadd(ww, 1, StartDate)<= @eDate )
SELECT WeekNum,
       CASE
           WHEN DatePart(YEAR, StartDate) < DATEPART(YEAR,@sDate) 
                THEN CAST(Cast(DatePart(YEAR,@sdate) AS varchar(4)) + '-1-1' AS Date)
           WHEN DatePart(MONTH, StartDate) < DATEPART(MONTH,@sDate) 
                THEN CAST(Cast(DatePart(YEAR,@sdate) AS varchar(4)) + '-' 
                + Cast(DatePart(MONTH,@sdate) AS varchar(2)) + '-1' AS Date)
           ELSE StartDate
       END AS StartDate,
       CASE
           WHEN DatePart(MONTH, EndDate) > DATEPART(MONTH,@eDate) THEN @eDate
           ELSE EndDate
       END AS EndDate
FROM cte

This works good - the only problem is that sunday is taken as the first day. — FrenkyB, Mar 14 '17 at 19:57
Problem is that it returns 7 seven days for a week, which is not always the case. — FrenkyB, Mar 14 '17 at 20:00
Hi FrenkyB hope this is what you are looking for. If you think this solves it please mark as answered. Thanks. — CodeEnhancer, Mar 14 '17 at 21:19
Enhanced it to work work for two scenarios 1. New Year - Jan'2017 2. New Month is Sunday - April'2017 @FrenkyB - Will your week be 5 days instead of 7 days? Almost there to resolve it for 7 days if I am able to get through the Feb'2017 and March'2017 scenarios — CodeEnhancer, Mar 15 '17 at 00:47
@FrenkyB - It is working for all the possible scenarios that I can think. Sorry for multiple edits ( might have generated many notifications to you). — CodeEnhancer, Mar 15 '17 at 01:05

score 1 · Answer 5 · edited May 23 '17 at 12:26

It is very smart in such situations to use a numbers / date / tally table. Such a table is very handsome in many situations!

In this answer I show an approach how to create and fill such a table.

Once you have created this table, the query is as simple as:

SELECT n.CalendarDate AS StartOfWeek
      ,DATEADD(DAY,6,n.CalendarDate) AS EndOfWeek
FROM dbo.RunningNumbers AS n
WHERE n.CalendarDate>={d'2017-01-01'} AND n.CalendarDate<{d'2017-02-01'}
  AND n.CalendarWeekDay=1;

hint Such a table can easily be extended with information hardly to compute simply by adding some values into extra columns manually (e.g. holydays, free-of-work days...)

score 1 · Answer 6 · answered Mar 14 '17 at 21:49

So many variations on this theme. This one is a little shorter than other answers though.

declare @dt date = {fn current_date()};
declare @start_of_year date = datefromparts(year(@dt), 1, 1);

with digits(d) as (
    select 0 union all select 1 union all select 2 union all select 3 union all
    select 4 union all select 5 union all select 6 union all select 7
), wks as (
    select
        dateadd(week, d1.d * 8 + d0.d, dateadd(day, 1-datepart(weekday, @start_of_year), @start_of_year)) as week_start,
        d1.d * 8 + d0.d as wk
    from digits as d0 cross join digits as d1
)
select
    case when year(week_start) < year(@dt)
        then @start_of_year else week_start end as week_start,
    case when year(dateadd(day, 6, week_start)) > year(@dt)
        then datefromparts(year(@dt), 12, 31) else dateadd(day, 6, week_start) end as week_end
from wks
where wk between 0 and 53 and year(week_start) = year(@dt)
order by week_start;

Get start and end date for each week in month

6 Answers6

Linked