SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Weeks of the Month
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kwikwisi
Constraint Violating Yak Guru

278 Posts

Posted - 02/28/2013 :  09:08:45  Show Profile  Reply with Quote
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

Edited by - kwikwisi on 02/28/2013 09:12:20

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/28/2013 :  10:04:03  Show Profile  Reply with Quote

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/


Edited by - visakh16 on 02/28/2013 10:05:01
Go to Top of Page

kwikwisi
Constraint Violating Yak Guru

278 Posts

Posted - 02/28/2013 :  13:25:45  Show Profile  Reply with Quote
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/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/28/2013 :  23:31:35  Show Profile  Reply with Quote
welcome

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000