SQL Server - Find out the week of the month of the week of the year

I was interested in the question: if a date is given, how do you know which week of the month it belongs to? To know, for example, that May 7, 2009 is the second week of May, and June 15, 2009 is the third week of June. SQL Server has a built-in function that allows you to determine which week of the year (from 1 to 53) the given date belongs to. Maybe this will help? Here, for example, you can find out which week the previous month ended and deduct from the current one. For example, the last month ended on week 22, and the new one began on 23, next week 24, then 25 ... We subtract 22 from each week of the current month and get 1, 2, 3 ... week, respectively.

_ = __ - __

All this would be good if it were not for one thing. A new month almost always begins in the middle of the week. And if the last month ended on the 22nd week, then the current month has good chances to begin, too, from the 22nd week. If we start to subtract, we get months starting from zero (22 - 22), 0,1,2 ... disorder. It would be necessary to do something, so that if the week for which the old month ended and the new one started, then add one to the number of weeks. Take advantage of the division. If you divide the integers (int), then the result of the division will also be an integer, and the fractional part will be discarded. In this way

_ = __ + (__/__ - __)

Now the numbering of the weeks of the month will start correctly from one, not from scratch. Everything? Not yet. We missed the first month of the year, that is, January. If we begin to deduct the last week of the previous month from his first week, that is, the last week of December last year, then -52 (1 - 53) is not exactly what we need. Usually, to get rid of the unwanted number, it will be multiplied by zero. Do the same. We will do so if the first month is the number that needs to be taken away from its weeks multiplied by 0, and if the month is not the first, then it is interesting. It is interesting how one can turn a unit into zero, and all other numbers that are greater than one, into one. Here we also help the division of integers, in addition to the exponentiation. Like this:

2 - 2 1/X

Now the equation for finding the week of the month will look like this:
')
_ = __ + (__/__ - __)(2 - 2 1/_ )

Let's translate all this into Transact SQL

: DATEPART(wk, @dt)
: DATEPART(wk, DATEADD(MONTH, MONTH(@dt)-1, convert(date,DATENAME(yy, @dt))))
: DATEPART(wk, DATEADD(DAY, -1, DATEADD(MONTH, MONTH(@dt)-1, convert(date,DATENAME(yy, @dt)))))


We get this bulky expression:

DATEPART(wk, @dt) + (DATEPART(wk, DATEADD(DAY, -1, DATEADD(MONTH, MONTH(@dt)-1, convert(date,DATENAME(yy, @dt)))))/DATEPART(wk,
DATEADD(MONTH, MONTH(@dt)-1, convert(date,DATENAME(yy, @dt)))) - DATEPART(wk, DATEADD(DAY, -1, DATEADD(MONTH, MONTH(@dt)-1,
convert(date,DATENAME(yy, @dt))))))*(2 - POWER(2, 1/MONTH(@dt)))

If the @dt variable contains a date, this expression returns the week of the month (from 1 to 6) to which this date belongs. However, this is too cumbersome. In the vastness of the network, options are much shorter and more elegant. For example:

DATEPART(week, @dt) - DATEPART(week, DATEADD(month, DATEDIFF(month, 0, @dt), 0))+1 .

It seems to be working.