#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