0

I would need to find month's week number. I know the ways to find week number for year but not a month's.

Example: Sep 2019

Week 1 – 1st to 1st
Week 2 – 2nd to 8th
Week 3 – 9th to 15th
Week 4 – 16th to 22nd
Week 5 – 23rd to 29th
Week 6 – 30th to 30th

I tried finding one using below logic but it's taking 1st of Sep 2019 as 4th week. But I need the value as 1 here

declare @date datetime = '2019-09-01'
select datepart(day, datediff(day, 0, @date)/7*7)/7, datepart(day, datediff(day, 0, @date)/7 * 7)/7 + 1

Appreciate your help with this.

Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63
Lal
  • 11
  • 4
  • 3
    Possible duplicate: https://stackoverflow.com/questions/13116222/how-to-get-week-number-of-the-month-from-the-date-in-sql-server-2008 – Matt Evans Jul 11 '19 at 06:53
  • 1
    Possible duplicate of [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) – Pred Jul 11 '19 at 07:10

1 Answers1

0

Try with this

DECLARE @MyDate DATETIME
SET @MyDate = '2019-09-01'

SELECT DATEPART(WEEK, @MyDate) - DATEPART(WEEK, DATEADD(MM, DATEDIFF(MM,0,@MyDate), 0))+ 1 AS NoOfWeeks
Hardik Leuwa
  • 3,282
  • 3
  • 14
  • 28