4

I am trying to find the week number of a particular month given a date, so I want to know which week is that for given month

Example if I enter

  • 2016 Feb 2 ---> Week 1
  • 2016 Feb 9 ---> Week 2
  • 2016 June 2 ---> week 1
  • 2016 Jan 25 ---> week 5

Can I achieve this in a T-SQL query?

I have seen the following option 

DATEPART(wk, BookingTimeStamp)

But that gives the week number of the year, not the month

The idea is to build result per week for a given month

marc_s
  • 704,970
  • 168
  • 1,303
  • 1,425
makdu
  • 858
  • 1
  • 12
  • 25
  • Does this answer your question? [How to get week number of the month from the date in sql server 2008](https://stackoverflow.com/questions/13116222/how-to-get-week-number-of-the-month-from-the-date-in-sql-server-2008) – Ricardo M S Feb 26 '20 at 21:51
  • 1
    Accepted answer solved my problem – makdu Feb 27 '20 at 03:52

1 Answers1

5

The first result using "SQL Server and Weeks in Month" returned this article. It shows two ways using DATEPART along with other date parsing functions. Here is one solution:

DECLARE @MyDate DATETIME =GETDATE()

SELECT DATEDIFF(WEEK, DATEADD(MONTH, DATEDIFF(MONTH, 0, @MyDate), 0), @MyDate) +1
Community
  • 1
  • 1
Ross Bush
  • 13,809
  • 2
  • 33
  • 52