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)
 Week No

Author  Topic 

sanjay5219
Posting Yak Master

240 Posts

Posted - 2013-12-03 : 15:05:49
Hi All,

Is there a way if I can know the Week No.Datepart(wk,'') gives incremental no but I am looking week no based on month.

For example today is 4th Dec 2013 so I should get Week1

Please suggest

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-03 : 15:24:51
nothing out of the box to do that. But maybe if you subtract the week number of the beginning of the month from the current week number?

declare @dt datetime; set @dt = '2013-12-04'
select datepart(week, @dt) - datepart(week, dateadd(month, datediff(month, 0, @dt), 0)) + 1

output:
1



Be One with the Optimizer
TG
Go to Top of Page

sanjay5219
Posting Yak Master

240 Posts

Posted - 2013-12-04 : 11:48:56
thanks
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-04 : 13:09:35
you're welcome.

Be One with the Optimizer
TG
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-12-05 : 05:49:13
SELECT 1 + (DATEPART(DAY, @dt) - 1) / 7



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
   

- Advertisement -