Date Range, reverse for WEEK() function in MySQL
In Mysql, we have a WEEK() function which returns the week number for a date.
WEEK(date, mode) returns the week number
This works very well, but there is no function for reverse engineering.
During my development, I struggled very hard to find a function which will accept the week number as an input and it will return the start date and the end date for the week. But there’s no specific function to achieve that.
So, the question or the challenge was: How to get the date range as a start date and end date for a week if we give the week number as an input in MySQL.
Refer to the below SQL query :
DATE_ADD(date, INTERVAL(1-DAYOFWEEK(date)) DAY) as start_date
DATE_ADD(date, INTERVAL(7-DAYOFWEEK(date)) DAY) as end_date
"select WEEK(date) as week," \
"sum(marks) as marks, sum(marks2) as marks2," \
"sum(marks3) as marks3,sum(marks4) as marks4," \
"sum(marks5) as marks5, sum(marks6) as marks6, " \
"DATE_ADD(date, INTERVAL(1-DAYOFWEEK(date)) DAY) as start_date," \
"DATE_ADD(date, INTERVAL(7-DAYOFWEEK(date)) DAY) as end_date " \
"from test_ashu_marks where subject not like '%math%' and " \
"date >= DATE_ADD(CURDATE(), INTERVAL - 31 DAY) " \
"group by week,start_date,end_date order by week desc"The above query snippet works very well and is running live in production with no issues.
Feel free to reach out to me at : https://www.linkedin.com/in/ashutosh-kumar-9a109864/
