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"?

Accepted Answer

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

Solution