Update date to 1st day of the month in MySQL?

date, mysql, sql, sql-update

Solution

You can convert it to string to get the year and month and concatenate it with `01`.

UPDATE  tableName
SET     dateField = CONCAT(DATE_FORMAT(dateField, '%Y-%m-'), '01')

- SQLFiddle Demo

Problem

How would I go about updating the dates in a MySQL table to the first day of the month? For example, the data looks like this: ``` 1 2013-01-13 2 2013-02-11 3 2013-02-01 4 2013-01-30 5 2013-03-27 ``` ...and I would like it to look like this... ``` 1 2013-01-01 2 2013-02-01 3 2013-02-01 4 2013-01-01 5 2013-03-01 ```

Original source