user3872094 user3872094 -4 years ago 121
SQL Question

Confusing result in SQL Server

I have these queries:

String sql = "update time_tracker set logout = ?, totaltime = DATEDIFF(second, login, logout) from Time_Tracker where userid= ? AND CONVERT(Date, LOGIN) = CONVERT(Date, GETDATE())";


select DATEDIFF(second, login, logout) as timeDiff
from Time_Tracker

When I run the first query and do a
select * from table
I get the below output.

enter image description here

But when I run the second query it turns out to be

enter image description here

This is quite confusing, please let me know where am I going wrong and how can I fix this.


Answer Source

This part of the query is incorrect:

set logout = ?, totaltime = DATEDIFF(second, login, logout)

This is setting the logout to the new value from the param, but setting the totaltime equal to the difference in seconds between the login and the previously-existing logout value. Which is why you're seeing only 394, because the previous logout value must have been 2016-08-25 20:23:28.000.

To remedy this, you should pass in the same ? param to the DATEDIFF() function:

set logout = ?, totaltime = DATEDIFF(second, login, ?)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download