How do I get the first day of the week of a date in mysql?

If you need to handle weeks which start on Mondays, you could also do it that way. First define a custom FIRST_DAY_OF_WEEK function:

DELIMITER ;;
CREATE FUNCTION FIRST_DAY_OF_WEEK(day DATE)
RETURNS DATE DETERMINISTIC
BEGIN
  RETURN SUBDATE(day, WEEKDAY(day));
END;;
DELIMITER ;

And then you could do:

SELECT FIRST_DAY_OF_WEEK('2011-01-03');

For your information, MySQL provides two different functions to retrieve the first day of a week. There is DAYOFWEEK:

Returns the weekday index for date (1 = Sunday, 2 = Monday, …, 7 = Saturday). These index values correspond to the ODBC standard.

And WEEKDAY:

Returns the weekday index for date (0 = Monday, 1 = Tuesday, … 6 = Sunday).

If week starts on Monday

  SELECT SUBDATE(mydate, weekday(mydate));

If week starts on Sunday

  SELECT SUBDATE(mydate, dayofweek(mydate) - 1);

Example:

SELECT SUBDATE('2018-04-11', weekday('2018-04-11'));

2018-04-09

SELECT SUBDATE('2018-04-11', dayofweek('2018-04-11') - 1);

2018-04-08

If the week starts on Sunday do this:

DATE_ADD(mydate, INTERVAL(1-DAYOFWEEK(mydate)) DAY)

If the week starts on Monday do this:

DATE_ADD(mydate, INTERVAL(-WEEKDAY(mydate)) DAY);

more info

Week starts day from sunday then get First date of the Week and Last date of week

SELECT  
  DATE("2019-03-31" + INTERVAL (1 - DAYOFWEEK("2019-03-31")) DAY) as start_date,  
  DATE("2019-03-31" + INTERVAL (7 - DAYOFWEEK("2019-03-31")) DAY) as end_date

Week starts day from Monday then get First date of the Week and Last date of week

SELECT  
  DATE("2019-03-31" + INTERVAL ( - WEEKDAY("2019-03-31")) DAY) as start_date, 
  DATE("2019-03-31" + INTERVAL (6 - WEEKDAY("2019-03-31")) DAY) as end_date