CODEWITHSUNDEEP
mysqlsqldatesql-update
dahamsta
dahamsta

Reputation: 103

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

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

Upvotes: 10

Views: 7564

Answers (2)

CTD
CTD

Reputation: 336

UPDATE tableName SET date = DATE_SUB(date,INTERVAL DAYOFMONTH(date)-1 DAY)

Date and Time Functions

Upvotes: 3

John Woo
John Woo

Reputation: 263693

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')

Upvotes: 23

Related Questions