Weeks of the Month - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Weeks of the Month

Author

Topic

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2013-02-28 : 09:08:45

Can anyone help on udf to return weeks of the month.
It accepts parameter year and month, returns

Week StartDate
Week1 01/03/2013
Week2 08/03/2013
Week3 13/03/2013
.
.

Tks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-28 : 10:04:03

[code]
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)
[/code]

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

kwikwisi
Constraint Violating Yak Guru

283 Posts

Posted - 2013-02-28 : 13:25:45

Thanks !

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/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-28 : 23:31:35

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources