I am having some difficulty getting the difference between two time values in SQL. I want to select all the values that have less than 5 seconds difference between the start and stop of the date value.
My data looks like this
Stop
-----------------------
2017-10-04 16:32:06:000
Start
-----------------------
2017-10-04 16:32:02:000
The above scenario would be one of those calls. My code in the where clause to obtain only values in the data that are less than 5 seconds:
datediff(second, cast(stop as int), cast(start as int)) < 5
I also tried just datediff without the cast as int and still received the same error:
Conversion failed when converting the varchar vlue '2017-10-04 20:58:46:000' to data type int
Instead of using datediff() add seconds to the start time:
where stop < dateadd(second, 5, start)
You should be storing the date/times as date/time types, not as strings. However, you at least have a standard format, so you can be explicit about the type casts if you like:
where convert(datetime, stop) < dateadd(second, 5, convert(datetime, start))
This is actually more accurate, because datediff() counts "time boundaries" between two values, not actual seconds.
The main thing with datediff(), though, is that the arguments are not integers. So you could do:
datediff(second, start, stop) < 5
But the first method is better.