How to get start and end week date using week number in mysql?

Question

I can easily get the start date and end of week like below.

SELECT
    COUNT(*) AS reports_in_week,
    DATE_ADD(mydate, INTERVAL(1-DAYOFWEEK(mydate)) DAY),
    DATE_ADD(mydate, INTERVAL(7-DAYOFWEEK(mydate)) DAY)
FROM mytable
where WEEK(mydate,1) = '29'
GROUP BY WEEK(mydate,1)

but the problem is that case indexing of column date is not working, if i pass date range between indexing will work, i try to get the start date and end date of week using week number;

I am starter in mysql please help.

Answer 1

If you want to filter on a given week and year, then one option is to use str_to_date() to generate a date range from a year and week number:

where 
        mydate >= str_to_date('2020-29 Monday', '%Y-%u %W')
    and mydate <  str_to_date('2020-29 Monday', '%Y-%u %W') + interval 7 day

The %u specifier represents a week number, with Monday being the first day of the week (so this is like WEEK() in mode 1, which your query uses).

The above where predicate would take advantage of an index on mydate - which seems to be your purpose here.