MySQL round date to the start of the week and month

Question

For "2012-07-12", how can I get the start of the week, i.e., "2012-07-08", and start of the month, i.e., "2012-07-01"?

Answer 1

First day of the month:

SELECT DATE_FORMAT('2007-07-12', '%Y-%m-01');

output: 2007-07-01

First day of the week:

SELECT DATE_SUB('2007-07-12', INTERVAL DAYOFWEEK('2007-07-12')-1 DAY);

output: 2007-07-08

MySQL reference: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-add

Answer 2

For those who need Monday as the first day of the week:

SELECT DATE_SUB('2007-07-12', INTERVAL WEEKDAY('2007-07-12') DAY);

output: 2007-07-09

This relies on the WEEKDAY function, which starts with Monday instead of DAYOFWEEK, which starts with Sunday.

Answer 3

Same answer as Borophyll's, but I have changed the behavior of the first day of the month to return a date, not just a string which avoids date formatting/parsing mentioned in user151220's answer.

First day of the month:

SELECT DATE_SUB('2007-07-12', INTERVAL DAYOFMONTH('2007-07-12') - 1 DAY);

output: 2007-07-01

First day of the week:

SELECT DATE_SUB('2007-07-12', INTERVAL DAYOFWEEK('2007-07-12') - 1 DAY);

output: 2007-07-08

MySQL reference: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-add