Set current date in the datetime field

Question

There is a datetime field in the MySQL table:

`mytime` datetime

It contains entries like '2012-02-10 10:15'.

How to set the date part to the current date?

Possible duplicate of http://stackoverflow.com/questions/1138928/mysql-set-current-date-in-a-datetime-field-on-insert?rq=1 — Bharath Mg, Jan 07 '13 at 13:45

Suresh Kamrushi · Accepted Answer · 2020-05-12T06:55:43.707

13

You can use -

update table tblName set mytime = current_date()

Or

update table tblName set mytime =concat(current_date(),' ',TIME(mytime))

edited May 12 '20 at 06:55

answered Jan 07 '13 at 13:46

Suresh Kamrushi

15,627
13
75
90

This statement set the time part to 00:00:00. – You Kuper Jan 07 '13 at 13:49
@YouKuper added optional query you can use it. – Suresh Kamrushi Jan 07 '13 at 13:52
It says Incorrect datetime value: '2013-01-0721:15:00' for column 'mytime' at row 1 – You Kuper Jan 07 '13 at 13:54
1

This one works update schedule set mytime =concat(current_date(), ' ', TIME(mytime)) – You Kuper Jan 07 '13 at 13:55
1

@JohnCruz sorry for the typo. – Suresh Kamrushi May 12 '20 at 06:56

score 5 · Answer 2 · answered Jun 20 '19 at 10:39

5

Use below query..

update Table1 set mytime=now();

answered Jun 20 '19 at 10:39

Siddharth Reddy

51
1
1

John Woo · Answer 3 · 2013-01-07T13:58:08.503

2

UPDATE Table1
SET mytime = CONCAT_WS(' ',CURDATE(), TIME(myTime))

SQLFiddle Demo

edited Jan 07 '13 at 13:58

answered Jan 07 '13 at 13:48

John Woo

258,903
69
498
492

score 2 · Answer 4 · edited Jun 21 '19 at 07:29

2

UPDATE table SET mytime = CONCAT(CURDATE(), ' ' , time(mytime)) WHERE id = row;

I think this will work, my syntax may be off. time(mytime) may give you hour:minute:seconds while you are only looking for hour:minute. I think you also need the ' ' in there so MySQL will recognize the time format.

edited Jun 21 '19 at 07:29

barbsan

3,418
11
21
28

answered Jan 07 '13 at 13:51

user1955162

797
2
6
21

Fathah Rehman P · Answer 5 · 2013-01-07T14:20:03.773

0

suppose your table is as follows

CREATE TABLE `table66` (
    `id` INT(10) NULL DEFAULT NULL,
    `mytime` DATETIME NULL DEFAULT NULL
)

Then you can use following query to update your mytime column

update table66 set mytime=concat(date(now()),' ',time(mytime) )

edited Jan 07 '13 at 14:20

answered Jan 07 '13 at 13:56

Fathah Rehman P

8,401
4
40
42

ypercubeᵀᴹ · Answer 6 · 2013-01-07T14:25:00.177

0

UPDATE test
SET mytime = mytime + INTERVAL DATEDIFF(CURRENT_DATE(),DATE(mytime)) DAY  ;

edited Jan 07 '13 at 14:25

answered Jan 07 '13 at 14:10

ypercubeᵀᴹ

113,259
19
174
235

score 0 · Answer 7 · answered Jul 29 '22 at 06:55

The question is quite vague but comes up top on a Google Search.

In general just use now() to set a datetime column with the current date AND time. Whether an insert, update or trigger it doesn't matter.

Several answers here have updates without a where clause. Most updates would have a where clause so as not to affect the whole table.

Set current date in the datetime field

7 Answers7