Update Only Time in a MySQL Datetime Field

How to update only the hour from a DATETIME field in MySQL?

SQL

UPDATE datetimes
SET datetime = DATE_ADD(datetime,
                        INTERVAL (15 - HOUR(datetime)) HOUR);

Demo

http://rextester.com/JOJWJ94999

Explanation

DATE_ADD(datetime, INTERVALintervalHOUR) adds or subtracts interval hours from datetime (depending on whether interval is positive or negative). The number of hours to add or subtract is calculated by subtracting the number of hours part of datetime (found from HOUR(datetime)) from 15. If the current time is 16:00 or after, this will be negative and if the current time is before 15:00, it will be a positive number. There is no WHERE clause so all rows in the table will be updated.

Mysql Datetime field, update only date using php

Try with this query: UPDATE INVHDR_edit SET Invdate='".$new." 00:00:00' WHERE usr='$user'");

if you need time to append use following.

$new = $new.date(" H:i:s");
mysql_query("UPDATE INVHDR_edit SET Invdate='$new' WHERE usr='$user'");

update time to date-time column in MySQL

Use DATE_ADD which requires two parameters, first is the initial datetime and second paramter comprises of the keyword INTERVAL, the quantity to be added and the unit of quantity to be added.

In your case the source time would be responseDateTime and second parameter would be INTERVAL 5 HOUR. It returns the updated datetime

UPDATE tblswitchemprequest SET responseDateTime = DATE_ADD(responseDateTime, INTERVAL 5 HOUR)

http://www.w3schools.com/sql/func_date_add.asp

MySQL DATETIME - Change only the date

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

How can I update date of datetime field with mysql only?

One option is to use this:

UPDATE `calldata`
SET `date` = DATE_SUB(`date`, INTERVAL 7 DAY)
WHERE DATE(`date`) = '2014-01-08'

Fiddle: http://sqlfiddle.com/#!2/28d71/1

For better performance use the following query because index (if any) can be used by MySQL as DATE() is not on the left side of comparison operator:

UPDATE `calldata`
SET `date` = DATE_SUB(`date`, INTERVAL 7 DAY)
WHERE `date` >= '2014-01-08'
AND `date` < '2014-01-09

Fiddle: http://sqlfiddle.com/#!2/7e7b2/1

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

You can make use of MySQL CONCAT() function.

Your update query would looks something like this,

UPDATE table_name
SET
date_column = CONCAT('2016-06-29 15:',MINUTE(date_column),':',SECOND(date_column))
WHERE
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.

http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_concat

Update only the time portion of a MySQL TIMESTAMP

UPDATE tab 
SET ts_field = ADDTIME(DATE(ts_field), new_time) 
WHERE id = ?;