MySQL DATETIME - Change only the date

Question

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

Will the year always remain the same? – rownage Jan 14 '11 at 19:37 — rownage, Jan 14 '11 at 19:37

score 68 · Accepted Answer · edited Jun 09 '15 at 16:34

68

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

edited Jun 09 '15 at 16:34

Mohammed H

6,880
16
81
127

answered Jan 14 '11 at 20:46

itsmeee

1,627
11
12

2

Saved me some good stress. Works well with WHERE condition(s) too. – OmniPotens Jun 16 '18 at 04:58

score 18 · Answer 2 · edited Jan 14 '11 at 20:08

18

To change it 5 days ahead:

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

(you can use MONTH, YEAR, etc too)

edited Jan 14 '11 at 20:08

Raj More

47,048
33
131
198

answered Jan 14 '11 at 19:57

rownage

2,392
3
22
31

1

For some reason, my answer wouldn't submit when i had the word "update" above spelled correctly. Weird. – rownage Jan 14 '11 at 19:59

score 3 · Answer 3 · answered Jan 14 '11 at 19:39

3

Probably, DATE_ADD is a good idea. link text

answered Jan 14 '11 at 19:39

Paul

2,972
2
21
16

score 2 · Answer 4 · edited Sep 20 '17 at 09:23

2

Check Query

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

edited Sep 20 '17 at 09:23

Aman Kumar

4,533
3
18
40

answered Jul 17 '13 at 10:29

Krishna-Winnou systems

131
1
3

score 1 · Answer 5 · answered Jan 14 '11 at 19:39

1

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

answered Jan 14 '11 at 19:39

Raj More

47,048
33
131
198

score 1 · Answer 6 · edited Sep 20 '17 at 08:58

1

Going to use something like:

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

edited Sep 20 '17 at 08:58

Aman Kumar

4,533
3
18
40

answered Jan 14 '11 at 20:04

Michael

1,816
7
21
35

score 0 · Answer 7 · answered Feb 06 '22 at 04:47

0

Just Use:

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

answered Feb 06 '22 at 04:47

Mehraab Hossain

68
6

MySQL DATETIME - Change only the date

7 Answers7

Going to use something like:

Linked