Wednesday, June 4, 2014

Get week start date and week end date of a month in SQL server



#First week of particular month will be start from 1st day of month and last week of month will be end at month end date    

    with C(i) as
              ( select CONVERT(VARCHAR(10),CAST(@monthStart as datetime),111) i
                UNION ALL
                select CONVERT(VARCHAR(10),DATEADD (day,1,i),111) i from C
                     where DATEADD (day,1,i)
                             <DATEADD(month,1,@monthStart)
              ), C1 as
              (
              select DATEPART(WEEK,i)-DATEPART(WEEK,@monthStart)+1 WeekOfMonth,i from C
              )
              select WeekOfMonth,min(i) StartDate, max(i) EndDate from C1 group by WeekOfMonth

No comments:

Post a Comment