how to find Nth week in a month for a given date in sql server?

• Biswa Mahapatra

  Old Hand

  Points: 382

  More actions

  July 5, 2005 at 12:26 pm

  #68151

  Hi All,

  Can anybody tell me given a date is which Nth week of the month. And also i need to find no. of weeks in that month.

  Help appreciated.

• Farrell Keough

  SSCoach

  Points: 17414

  More actions

  July 5, 2005 at 12:34 pm

  #570972

  SQL Server has a week function, but you may find it rather odd to work with.   

  Try the following and see what you get. 

  SELECT DATEPART( week, '01/01/2005')

  SELECT DATEPART( week, '01/02/2005')

   

  (non-bulldozer response......) 

  I wasn't born stupid - I had to study.

• Biswa Mahapatra

  Old Hand

  Points: 382

  More actions

  July 5, 2005 at 12:35 pm

  #570973

  but that shows week of the year..not for the month...

• noeld

  SSC Guru

  Points: 96590

  More actions

  July 5, 2005 at 12:43 pm

  #570974

  Have a look:

  http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=108278

  
  * Noel

• Biswa Mahapatra

  Old Hand

  Points: 382

  More actions

  July 5, 2005 at 12:49 pm

  #570978

  Noel, Thanks a lot !!!

• Kenneth Wilhelmsson

  SSC-Dedicated

  Points: 30043

  More actions

  July 6, 2005 at 2:25 am

  #571130

  Just note that weeknumbers are ambigous on a global scale. Different countries have different rules on when week one starts for a given year, and also in which weeknumber a year ends as a consequence.

  Also, (though this may be clear to you in your case) when asking a question like - how many weeks are there in a month? - you also must establish rules of 'what is a week'. Is it a 7-day period? Is it first-last weekday according to your chosen national calendar rules? Must the entire week be within the month? etc etc..

  

  /Kenneth

• Jorge Cordero

  Ten Centuries

  Points: 1144

  More actions

  July 7, 2005 at 2:15 am

  #571667

  declare @d datetime, @n int

  set @d = '2005-12-15' -- to say anything

  set @n = datepart(wk,@d) + 1 - datepart(wk,dateadd(d, 1-day(@d),@d ))

  select @n -- the nth week...

• Jorge Cordero

  Ten Centuries

  Points: 1144

  More actions

  July 7, 2005 at 2:29 am

  #571670

  And, not tested but, the number of weeks per month

  declare @d datetime, @n int

  set @d = '2005-12-15' -- sample date

  set @n = datepart(wk,dateadd(d,-1,dateadd(m,1,dateadd(d, 1-day(@d),@d )))) - datepart(wk,dateadd(d, 1-day(@d),@d )) +1

  select @n -- the no of weeks in month(@d)...