Search code examples
mysqlsqldatetimedateadd

Add An Hour To TIME Datatype

UPDATE Game
SET endTime = (SELECT DATEADD(hh, 1, startTime)
FROM GameInfo
WHERE Game.idGame = GameInfo.idGame);

I want to set the hours of endTime to be 1 hour later than the startTime, when I run this code I get the error:

ERROR 1728 (HY000): Cannot load from mysql.proc. The table is probably corrupted

I believe this is because the startTime and endTime fields are both of datatype TIME, but I can't seem to find a similar function for TIME datatype? 

UPDATE Game
SET endTime = (SELECT HOUR(startTime)+1
FROM GameInfo
WHERE Game.idGame = GameInfo.idGame);

Will almost work, in that it does increment the hours, but then it saves just the hours as a number (resulting in 00:00:19 etc. for all entries).

Any help would be most appreciated!

Thanks.

Solution

  • What you are looking for is: DATE_ADD(startTime, INTERVAL 1 HOUR)