Get start and end date for each week in month, Sql Server

Get start and end date for each week in month

Language: Layout:

declare @fromdate date = '20161220'
    declare @thrudate date = '20171231'

;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      /* 100 days */
          --cross join n as kilo     /* 2.73 years */
          --cross join n as [tenK]    /* 27.3 years */
       order by 1
    )

select 
    convert(varchar(10),DateValue,120)
  , 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) -6,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) ,DateValue) 
      end) ),120)
from dates

View schema
Execution time: 0 sec, rows selected: 102, rows affected: 0, absolute service time: 0,19 sec

	(No column name)	WeekStart	WeekEnd
1	2016-12-20	2016-12-18	2016-12-24
2	2016-12-21	2016-12-18	2016-12-24
3	2016-12-22	2016-12-18	2016-12-24
4	2016-12-23	2016-12-18	2016-12-24
5	2016-12-24	2016-12-18	2016-12-24
6	2016-12-25	2016-12-25	2016-12-31
7	2016-12-26	2016-12-25	2016-12-31
8	2016-12-27	2016-12-25	2016-12-31
9	2016-12-28	2016-12-25	2016-12-31
10	2016-12-29	2016-12-25	2016-12-31
11	2016-12-30	2016-12-25	2016-12-31
12	2016-12-31	2016-12-25	2016-12-31
13	2017-01-01	2017-01-01	2017-01-07
14	2017-01-02	2017-01-01	2017-01-07
15	2017-01-03	2017-01-01	2017-01-07
16	2017-01-04	2017-01-01	2017-01-07
17	2017-01-05	2017-01-01	2017-01-07
18	2017-01-06	2017-01-01	2017-01-07
19	2017-01-07	2017-01-01	2017-01-07
20	2017-01-08	2017-01-08	2017-01-14
21	2017-01-09	2017-01-08	2017-01-14
22	2017-01-10	2017-01-08	2017-01-14
23	2017-01-11	2017-01-08	2017-01-14
24	2017-01-12	2017-01-08	2017-01-14
25	2017-01-13	2017-01-08	2017-01-14
26	2017-01-14	2017-01-08	2017-01-14
27	2017-01-15	2017-01-15	2017-01-21
28	2017-01-16	2017-01-15	2017-01-21
29	2017-01-17	2017-01-15	2017-01-21
30	2017-01-18	2017-01-15	2017-01-21
31	2017-01-19	2017-01-15	2017-01-21
32	2017-01-20	2017-01-15	2017-01-21
33	2017-01-21	2017-01-15	2017-01-21
34	2017-01-22	2017-01-22	2017-01-28
35	2017-01-23	2017-01-22	2017-01-28
36	2017-01-24	2017-01-22	2017-01-28
37	2017-01-25	2017-01-22	2017-01-28
38	2017-01-26	2017-01-22	2017-01-28
39	2017-01-27	2017-01-22	2017-01-28
40	2017-01-28	2017-01-22	2017-01-28
41	2017-01-29	2017-01-29	2017-02-04
42	2017-01-30	2017-01-29	2017-02-04
43	2017-01-31	2017-01-29	2017-02-04
44	2017-02-01	2017-01-29	2017-02-04
45	2017-02-02	2017-01-29	2017-02-04
46	2017-02-03	2017-01-29	2017-02-04
47	2017-02-04	2017-01-29	2017-02-04
48	2017-02-05	2017-02-05	2017-02-11
49	2017-02-06	2017-02-05	2017-02-11
50	2017-02-07	2017-02-05	2017-02-11
51	2017-02-08	2017-02-05	2017-02-11
52	2017-02-09	2017-02-05	2017-02-11
53	2017-02-10	2017-02-05	2017-02-11
54	2017-02-11	2017-02-05	2017-02-11
55	2017-02-12	2017-02-12	2017-02-18
56	2017-02-13	2017-02-12	2017-02-18
57	2017-02-14	2017-02-12	2017-02-18
58	2017-02-15	2017-02-12	2017-02-18
59	2017-02-16	2017-02-12	2017-02-18
60	2017-02-17	2017-02-12	2017-02-18
61	2017-02-18	2017-02-12	2017-02-18
62	2017-02-19	2017-02-19	2017-02-25
63	2017-02-20	2017-02-19	2017-02-25
64	2017-02-21	2017-02-19	2017-02-25
65	2017-02-22	2017-02-19	2017-02-25
66	2017-02-23	2017-02-19	2017-02-25
67	2017-02-24	2017-02-19	2017-02-25
68	2017-02-25	2017-02-19	2017-02-25
69	2017-02-26	2017-02-26	2017-03-04
70	2017-02-27	2017-02-26	2017-03-04
71	2017-02-28	2017-02-26	2017-03-04
72	2017-03-01	2017-02-26	2017-03-04
73	2017-03-02	2017-02-26	2017-03-04
74	2017-03-03	2017-02-26	2017-03-04
75	2017-03-04	2017-02-26	2017-03-04
76	2017-03-05	2017-03-05	2017-03-11
77	2017-03-06	2017-03-05	2017-03-11
78	2017-03-07	2017-03-05	2017-03-11
79	2017-03-08	2017-03-05	2017-03-11
80	2017-03-09	2017-03-05	2017-03-11
81	2017-03-10	2017-03-05	2017-03-11
82	2017-03-11	2017-03-05	2017-03-11
83	2017-03-12	2017-03-12	2017-03-18
84	2017-03-13	2017-03-12	2017-03-18
85	2017-03-14	2017-03-12	2017-03-18
86	2017-03-15	2017-03-12	2017-03-18
87	2017-03-16	2017-03-12	2017-03-18
88	2017-03-17	2017-03-12	2017-03-18
89	2017-03-18	2017-03-12	2017-03-18
90	2017-03-19	2017-03-19	2017-03-25
91	2017-03-20	2017-03-19	2017-03-25
92	2017-03-21	2017-03-19	2017-03-25
93	2017-03-22	2017-03-19	2017-03-25
94	2017-03-23	2017-03-19	2017-03-25
95	2017-03-24	2017-03-19	2017-03-25
96	2017-03-25	2017-03-19	2017-03-25
97	2017-03-26	2017-03-26	2017-04-01
98	2017-03-27	2017-03-26	2017-04-01
99	2017-03-28	2017-03-26	2017-04-01
100	2017-03-29	2017-03-26	2017-04-01