Code :-
SELECT employee_id,hire_date, weekday(hire_date) as weekday_count,
WEEKDAY(concat(YEAR(hire_date),'-',MONTH(hire_date),'-01')) as first_day_of_month,
FLOOR((DAYOFMONTH(hire_date)-1 + WEEKDAY(concat(YEAR(hire_date),'-',MONTH(hire_date),'-01')))/7) + 1 as week_of_month
FROM `employees` order by hire_date desc limit 0,10
-------------------------------
MYSQL Tutorial | Get Week Count Of Month Based On Date
-------------------------------
Subscribe my Channel:-
http://www.youtube.com/user/SanketRooney?sub_confirmation=1
Facebook Page:-
https://www.facebook.com/UnpossibleNS
Twitter Account:-
https://twitter.com/UnpossiblePOG
Blog:-
https://unpossiblepog.blogspot.com/
Website:-
https://unpossiblepog.com/
Gaming Instagram:- (@unpog.gaming)
https://www.instagram.com/unpog.gaming/
-------------------------------
While I was experimenting with MYSQL queries, I wondered if I could get week of the month from a give date by using the MYSQL query rather than using backend languages like PHP & JAVA. So I created a query for it.
Here is the simple employees table with more than 200 thousand rows.
And it does have a column called “hire_date” with a data type as date.
What I am going to do is just copy-paste the query that I’ve created.
Of course, you can copy-paste the code from the link given in the description.
Please note that in my case, Monday as a first day and Sunday as a last day of the week.
From FLOOR to WEEK_OF_MONTH is the main formula which will give you exact week count.
Let’s check the output.
The 20th of September 2022 belongs in 4th week.
The 11th of September 2022 represents 2nd week because it is the Sunday and also the last day of 2nd week.
You can replace the table and column name with your preferred table and columns to verify it.
Thanks for watching. Like share and subscribe.
