Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL DATETIME - Change only the date

Tags:

mysql

Starting with : 2011-01-17 09:30:00

Let's say I want to edit just the date with 2011-01-28

What is the most efficient way to end up with: 2011-01-28 09:30:00

Thanks!

For everyone saying Date_Add... that would require me to subtract the dates, then add the days. Thats a possibility... but was looking to remove that first step, and just "replace" the date

like image 648
Michael Avatar asked Jan 14 '11 19:01

Michael


Video Answer


4 Answers

If you really don't want to use date_add function, you can consider using this construction:

UPDATE table_name SET field_name = concat('2011-01-12 ', time(field_name)) 

Make sure to add a space after the date ('2011-01-12').

like image 62
itsmeee Avatar answered Oct 16 '22 20:10

itsmeee


To change it 5 days ahead:

UPDATE yourTableName
SET myDate1 = myDate1 + INTERVAL 5 DAY
WHERE myDate1 = dateIWantToChange

(you can use MONTH, YEAR, etc too)

like image 44
rownage Avatar answered Oct 16 '22 21:10

rownage


Probably, DATE_ADD is a good idea. link text

like image 3
Paul Avatar answered Oct 16 '22 20:10

Paul


Check Query

 update yourtable set eventtime=replace(eventtime,substr(eventtime,1,10), '2013-07-17')  WHERE  `id`=4
like image 2
Krishna-Winnou systems Avatar answered Oct 16 '22 22:10

Krishna-Winnou systems