DECLARE @Year int,@Month int

SELECT @Year=2013,@Month=3

;With CTE
AS
(
SELECT DATEADD(mm,@Month-1,DATEADD(yy,@Year-1900,0)) AS DateVal,1 AS WeekNo
UNION ALL
SELECT DATEADD(wk,1,DateVal),WeekNo + 1
FROM CTE
WHERE DATEADD(wk,1,DateVal) < DATEADD(mm,@Month,DATEADD(yy,@Year-1900,0))
)

SELECT DateVal,WeekNo
FROM CTE

OPTION (MAXRECURSION 0)

quote:

---

Originally posted by visakh16

DECLARE @Year int,@Month int

SELECT @Year=2013,@Month=3

;With CTE
AS
(
SELECT DATEADD(mm,@Month-1,DATEADD(yy,@Year-1900,0)) AS DateVal,1 AS WeekNo
UNION ALL
SELECT DATEADD(wk,1,DateVal),WeekNo + 1
FROM CTE
WHERE DATEADD(wk,1,DateVal) < DATEADD(mm,@Month,DATEADD(yy,@Year-1900,0))
)

SELECT DateVal,WeekNo
FROM CTE

OPTION (MAXRECURSION 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---