Determine the current week in a month

It is quiet easy to determine the current week in a year as shown below :

SELECT DATEPART( wk, GETDATE())

However, to determine the current week in a month, use this query written by AMB

select datediff(week, convert(varchar(6), getdate(), 112) + '01', getdate()) + 1

returns 5 as the date today is 24 December 2007
at

6 comments:

  1. PavanMay 7, 2008 at 5:26 AM

    Dear Friend,

    Its more helpful.I am very thankful for providing this valuable information.bye.....

    ReplyDelete
  2. AnonymousAugust 25, 2008 at 10:23 PM

    thanks a lot helped a lot

    ReplyDelete
  3. AnonymousFebruary 6, 2009 at 8:09 PM

    GRACIAS!!!!!
    Me ayudaste mucho con este dato.

    ReplyDelete
  4. AnonymousMay 28, 2009 at 2:04 PM

    This was extremely helpful! You saved my day!!!!

    ReplyDelete
  5. AnonymousJune 22, 2009 at 7:17 AM

    Hi ,

    I tested out this SQL Statement and it doesn't seem to work. My week starts on a Monday, so I set SQLs DATEFIRST to 1.

    I then ran the statement and declared the date as '2009-06-07'. When I ran the query it returned the value 2, which is incorrect. It should've returned 1, as the 7th June 2009 is still part of the first week that starts on a Monday and ends on a Sunday.

    Any suggestions on how to get around this?

    ReplyDelete
  6. AnonymousSeptember 13, 2010 at 4:58 AM

    Try this instead
    datediff(week, convert(varchar(6),@Date, 112) + '01', dateadd(dd,-1,@Date)) + 1

    ReplyDelete