user3872094 user3872094 - 3 months ago 8
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())";


And

select DATEDIFF(second, login, logout) as timeDiff
from Time_Tracker
where CONVERT(Date, LOGIN) = CONVERT(Date, GETDATE())


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.

Thanks.

Answer

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, ?)
Comments