How to get the time difference between two time values stored as strings?
Below is my table fields and its value fields; datatype is varchar.
I want the time difference between the ActualTimeIn and InTime (with hour:minute:seco开发者_C百科nd).
ActualTimeIn InTime
09:30:00 AM 1:23:00 PM
09:30:00 AM 11:30:00 AM
09:30:00 AM 11:29:00 AM
Expected output is like this:
LateComing
3:53:00
2:00:00
1:59:00
declare @T table(ActualTimeIn time(0), InTime time(0))
insert into @T values
('09:30:00 AM', '1:23:00 PM'),
('09:30:00 AM', '11:30:00 AM'),
('09:30:00 AM', '11:29:00 AM')
select cast(dateadd(s, datediff(s, ActualTimeIn, InTime), 0) as time(0))
from @T
Result
----------------
03:53:00
02:00:00
01:59:00
精彩评论