opLastTurn and TurnTime are DateTime Columns of mytable
SELECT ADDTIME(opLastTurn,TurnTime) AS result FROM mytable;
but it returns Null,what is wrong?
-
which data typesa are opLastTurn and TurnTime ? show a proper data sample and the expected resultScaisEdge– ScaisEdge2017-08-17 18:05:46 +00:00Commented Aug 17, 2017 at 18:05
-
@ scaisEdge they're type is datetimeAmir Rasti– Amir Rasti2017-08-17 18:07:20 +00:00Commented Aug 17, 2017 at 18:07
-
for both the columns ??ScaisEdge– ScaisEdge2017-08-17 18:07:42 +00:00Commented Aug 17, 2017 at 18:07
-
I think the 2nd argument must be a time data type. Or just the time value at least.SS_DBA– SS_DBA2017-08-17 18:08:06 +00:00Commented Aug 17, 2017 at 18:08
Add a comment
|
3 Answers
The first argument must be a datetime expression. The second must be a time expression. If they can't be parsed that way, the function gives up and returns NULL.
WRONG: Both arguments are datetime.
mysql> select addtime('2017-08-17 11:00:00', '2017-08-17 11:00:00');
+-------------------------------------------------------+
| addtime('2017-08-17 11:00:00', '2017-08-17 11:00:00') |
+-------------------------------------------------------+
| NULL |
+-------------------------------------------------------+
RIGHT: Second argument is time:
mysql> select addtime('2017-08-17 11:00:00', '11:00:00');
+--------------------------------------------+
| addtime('2017-08-17 11:00:00', '11:00:00') |
+--------------------------------------------+
| 2017-08-17 22:00:00 |
+--------------------------------------------+
Comments
The second arguments should be a time .. so try using
SELECT ADDTIME(opLastTurn, time(TurnTime) ) AS result FROM mytable;
1 Comment
Amir Rasti
dear scaisEdge i have tried it with a time column too but still returns null
I found the problem ,i get this result because some of my data was like below
datetime:0000-00-00 00:00:00 time:03:00:00 or datetime:2017-00-02 05:00:00 time:00:00:00 each row which has a default value for these field cased the null return
Thanks aloot @scaisEdge,Bill Karwin,Phil K