Need Week number for a particular date input

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 2005 Forums

Transact-SQL (2005)

Need Week number for a particular date input

Author

Topic

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2011-04-25 : 06:17:38

Hi All,

I need a function which inputs any date as input, the function should process and return a Week Number for that input date month as integer value as Output,
For ex: if we pass
1. 02/04/2011(DD/MM/YY any format) My output should be returned as 1
2. 06/04/2011(DD/MM/YY any format) My output should be returned as 2
ie from 03/04/2011 to 09/04/2011 should return as 2 as Week Number for that input date.

how to achieve this??

Thanks in Advance
Ganesh Kumar

Solutions are easy. Understanding the problem, now, that's the hard part

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-04-25 : 06:47:58

DECLARE @TestDate DATE
SET @TestDate = '04/02/2011'
SELECT DATEPART(WEEK,@TestDate) - DATEPART(WEEK,
CAST(DATEPART(MONTH, @TestDate) AS VARCHAR(2)) + '/01/'
+ CAST(DATEPART(YEAR, @TestDate) AS VARCHAR(4))) +1

--------------------------
http://connectsql.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-25 : 07:03:52

Yet another way:


DECLARE @TestDate DATE
SET @TestDate = '04/02/2011'
select 1+datediff(wk,-1,@TestDate)-datediff(wk,-1,dateadd(mm,datediff(mm,0,@TestDate),0))

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-04-25 : 07:09:46

quote:

Originally posted by sunitabeck

Yet another way:


DECLARE @TestDate DATE
SET @TestDate = '04/02/2011'
select 1+datediff(wk,-1,@TestDate)-datediff(wk,-1,dateadd(mm,datediff(mm,0,@TestDate),0))

Thats a cute one ;)

--------------------------
http://connectsql.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-25 : 07:23:14

quote:
Originally posted by lionofdezert

Thats a cute one ;)

--------------------------
http://connectsql.blogspot.com/

Heh! I have a thing against cast and convert with dates. Somehow it makes me uneasy, but at least in this case, your approach is more readable. I will look at mine 2 hours from now and ask myself "what was I trying to do there again?"

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2011-04-25 : 07:48:53

Thanks to all..

Solutions are easy. Understanding the problem, now, that's the hard part

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-25 : 07:59:03

I'm with Sunita. Why convert an integer to a string and then back in to an integer?

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources