I have a table with two columns login
and logout
. If a row has values "2019-08-07 20:37:12" in login
column and "2019-08-07 21:14:16" in logout
column, I want the difference between time values from login and logout columns.
I have:
SELECT logintime,CONVERT(varchar(6),DATEDIFF(second, login, logout)/3600)
+ ':'
+ RIGHT('0' + CONVERT(varchar(2),(DATEDIFF(second, login,logout) % 3600) / 60), 2)
+ ':'
+ RIGHT('0' + CONVERT(varchar(2),DATEDIFF(second, login, logout) % 60), 2)
AS 'HH:MM:SS' from face_login_logout
Expected result is '2019-08-07 01:23:04 '
.
5条答案
按热度按时间am46iovg1#
The easiest way for your example:
But it gives 00:37:04 as a result.
pu82cl6c2#
As @lypskee has suggested, I think your calculations are out.
I found the method below (which looks to be the same as your code) and it tells me that there is a difference of
0:37:04
update - adjusted for pulling from a table.
This would give you the result for each row in your table...you can adjust the query for any null values too.
Output:
v8wbuo2f3#
fae0ux8s4#
For SQL Server, if you're sure that the difference is less than 24 hours, you can use
timefromparts
to get the difference as atime
data type. I've also used cross apply to only write thedatediff
once instead of three times.First, create and populate sample table (Please save us this step in your future questions):
The query:
Results:
If you want a
datetime
as result, simply usedatetimefromparts
vktxenjb5#
Another easy way to subtract (or add) two
datetime
values in SQL Server is: