MySQL DATETIME – Change only the date

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

Contents hide

Going to use something like:

Method 7

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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◯‘).

Method 2

To change it 5 days ahead:

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

(you can use MONTH, YEAR, etc too)

Method 3

Probably, DATE_ADD is a good idea. link text

Method 4

Check Query

 update yourtable set eventtime=replace(eventtime,substr(eventtime,1,10), '2013-07-17')  WHERE  `id`=4

Method 5

You can add various components of a date to modify it using the Date_Add function. Check this out:

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-add

Method 6

Going to use something like:

CONCAT('2011-01-28 ',DATE_FORMAT(original_timestamp, '%H:%i:%s'))

Method 7

Just Use:

UPDATE table_name set column_name= DATE_FORMAT(column_name,'%Y-%m-28 %H:%i-%s');

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating