CODEWITHSUNDEEP
mysql
Michael
Michael

Reputation: 1866

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

Upvotes: 26

Views: 46988

Answers (7)

Mehraab Hossain
Mehraab Hossain

Reputation: 68

Just Use:

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

Upvotes: 0

Krishna-Winnou systems
Krishna-Winnou systems

Reputation: 131

Check Query

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

Upvotes: 2

Michael
Michael

Reputation: 1866

Going to use something like:

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

Upvotes: 1

itsmeee
itsmeee

Reputation: 1627

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

Upvotes: 68

rownage
rownage

Reputation: 2404

To change it 5 days ahead:

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

(you can use MONTH, YEAR, etc too)

Upvotes: 18

Paul
Paul

Reputation: 3010

Probably, DATE_ADD is a good idea. link text

Upvotes: 3

Raj More
Raj More

Reputation: 48016

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

Upvotes: 1

Related Questions