Click here to Skip to main content
15,662,823 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Week number of a year can be calculated using Datepart function, likewise how to calculate week number of a month.

What I have tried:

--Week number of a year
Updated 11-Jun-18 20:13pm

There is no specific "week number of a month" function - indeed, I've never seen one in any framework. Mostly I suspect because there is no standard definition of what constitutes a week number for a month (there is for "week of year", and it depends on the date of the first Thursday: ISO week date - Wikipedia[^]) but there is no equivalent for months, which will have the same problems. Is the first of March in week 1 of March, or week 3, 4, or 5 of February? Is the 29th February in week 4 or 5 of February, or week 1 of March? There is no standard definition, so no standard function.

You will have to decide the rules yourself, and then implement them. Good luck - it'll be a fun project...
Share this answer
Check this:

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

From: tsql - How to get week number of the month from the date in sql server 2008[^]
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900