Ara Ara - 1 year ago 60
SQL Question

How to update date and hour, while keeping Minutes and Seconds

I have a datetime

0000-00-00 00:00:00
column in my table,

Need to keep the minute and seconds when updating
____-__-__ __:MM:SS

but also change the date and hour to current time.

How can I achieve this on MySQL side?

Edit (Sample):

Current Date field value: 2016-06-27 15

We Update this table at 2016-07-28 12:31:18

Desired Date field value: 2016-07-28 12

As you can see the updated date still has correct
2016-07-28 12:
but the
(minutes and seconds) are Selected from the date before update and replaced by current time's minutes and seconds

Answer Source

You can make use of MySQL CONCAT() function.

Your update query would looks something like this,

UPDATE table_name
date_column = CONCAT('2016-06-29 15:',MINUTE(date_column),':',SECOND(date_column))
column_id = 1

CONCAT() returns the string that results from concatenating the given arguments.

So in your case we would update date and hour by adding first argument as 2016-06-29 15:, and then use the same minute and second from the same column. And concatenate all the arguments to make the new value as you need.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download