Get week number of month SQL Server

Question

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

Answer 1

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

Answer 2

I ran into the same problem when I wanted to get not the number of the week in the year, but the number of the week in relation to the month. In my solution, you can divide every day of date by 7. Then all the days of week 1 will be between 0 and 1. All days of week 2 are between 1 and 2, and so on.

This is a simple example of a practice query where you can use the case construct to find the number of the week.

Solution 1 (a bit confusing)

SELECT
    case
        when CAST(strftime('%d', date) as real) / 7 BETWEEN 0 AND 1 then 'week_1'
        when CAST(strftime('%d', date) as real) / 7 BETWEEN 1 AND 2 then 'week_2'
        when CAST(strftime('%d', date) as real) / 7 BETWEEN 2 AND 3 then 'week_3'
        when CAST(strftime('%d', date) as real) / 7 BETWEEN 3 AND 4 then 'week_4'
        when CAST(strftime('%d', date) as real) / 7 BETWEEN 4 AND 5 then 'a_bit_of_week_5'
    end as week
FROM cost
WHERE strftime('%Y', date) = '2022';

Solution 2 (simple)

SELECT
    case
       when CAST(strftime('%d', date) as integer) BETWEEN 1 AND 7 then 'week_1'
       when CAST(strftime('%d', date) as integer) BETWEEN 7 AND 14 then 'week_2'
       when CAST(strftime('%d', date) as integer) BETWEEN 14 AND 21 then 'week_3'
       when CAST(strftime('%d', date) as integer) BETWEEN 21 AND 28 then 'week_4'
       when CAST(strftime('%d', date) as integer) BETWEEN 28 AND 31 then 'a_bit_of_week_5'
    end as week
FROM cost
WHERE strftime('%Y', date) = '2022';

Deduction of solution 1

If you're just looking for a solution to an issue, then you don't need to read any further. I just want to tell you how I arrived at this solution and why you can trust it.

This solution can be visualized using the Python programming language.

from matplotlib import pyplot as plt 
days = [x for x in range(1, 32)]
days_on_seven = [x / 7 for x in range(1, 32)] # Divide each day by 7 
print(days_on_seven) 

## Result is below
 [0.14285714285714285,
 0.2857142857142857,
 0.42857142857142855,
 0.5714285714285714,
 0.7142857142857143,
 0.8571428571428571,
 1.0,
 1.1428571428571428,
 1.2857142857142858,
 1.4285714285714286,
 1.5714285714285714,
 1.7142857142857142,
 1.8571428571428572,
 2.0,
 2.142857142857143,
 2.2857142857142856,
 2.4285714285714284,
 2.5714285714285716,
 2.7142857142857144,
 2.857142857142857,
 3.0,
 3.142857142857143,
 3.2857142857142856,
 3.4285714285714284,
 3.5714285714285716,
...
 3.857142857142857,
 4.0,
 4.142857142857143,
 4.285714285714286,
 4.428571428571429]

Visualisation

As you can see, we get an array of increasing numbers. Let's graph the resulting numbers for each day of the month.

from matplotlib import pyplot as plt 
import seaborn as sns 


sns.lineplot(x=days, y=days_on_seven)
plt.xlabel("Number of day in month")
plt.ylabel("Result of division by 7")
plt.title("day / 7 plot")

The graph below by link

https://i.stack.imgur.com/SIVfT.png

The graph is very crude, but you can see, as noted earlier, that all the days of week 1 will be between 0 and 1. All days of week 2 are between 1 and 2, and so on.

I hope my decision was helpful and interesting to you.