MySQL - update the row's date and save the time

Question

I want to update a row which has timestamp datatype named date_start. Here's an example:

 date_start = 2017-04-26 12:34:11

I want to update it, but to save the time, so it'll look like this:

 date_start = 2017-05-28 12:34:11

Any ideas? So far I tried extract method and concat method.

Best Regards..

Is the problem that you don't know how to use `UPDATE` query or what? Your statement that you tried to use extract and concat method make absolutely no sense whatsoever. — Mjh, Jul 25 '17 at 08:16
I do know how to use `UPDATE` query, but I do not know how to update the date only in a timestamp field. — Stefan Kolev, Jul 25 '17 at 08:21
Possible duplicate of [MySQL DATETIME - Change only the date](https://stackoverflow.com/questions/4695117/mysql-datetime-change-only-the-date) — Malik, Jul 25 '17 at 08:37

Tim Biegeleisen · Accepted Answer · 2017-07-25T08:35:20.733

0

If you just want to update the date component of the timestamp while retaining the time component then you can build the updated timestamp using string concatenation with TIME():

UPDATE yourTable
SET date_start = CONCAT('2017-05-28 ', TIME(date_start))
WHERE <some condition>

Demo

edited Jul 25 '17 at 08:35

answered Jul 25 '17 at 08:22

Tim Biegeleisen

502,043
27
286
360

Hello. Thanks for the reply. This one turned the date_start field into 0000-00-00 00:00:00 . – Stefan Kolev Jul 25 '17 at 08:29
No, this should not have happened. Did you run my _exact_ query? – Tim Biegeleisen Jul 25 '17 at 08:30
Yes. here is the query: `UPDATE campaigns SET date_start = CONCAT('2017-07-25 ', TIME(date_start)) WHERE id = 918` – Stefan Kolev Jul 25 '17 at 08:30
It works for me in the demo. Give me a reproducible example and I will look at it. – Tim Biegeleisen Jul 25 '17 at 08:35

MySQL - update the row's date and save the time

1 Answers1

Demo