CODEWITHSUNDEEP
mysqlsql
Amir Rasti
Amir Rasti

Reputation: 768

MySQL: Passing DateTime Columns in functions

opLastTurn and TurnTime are DateTime Columns of mytable

SELECT ADDTIME(opLastTurn,TurnTime) AS result FROM mytable;

but it returns Null,what is wrong?

Upvotes: 1

Views: 40

Answers (3)

Amir Rasti
Amir Rasti

Reputation: 768

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

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562641

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                        |
+--------------------------------------------+

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133380

The second arguments should be a time .. so try using 

SELECT ADDTIME(opLastTurn, time(TurnTime) ) AS result FROM mytable;

Upvotes: 2

Related Questions