CODEWITHSUNDEEP
sqlsql-serverdatedatediffdateadd
user3120266
user3120266

Reputation: 425

Conversion failed when converting varchar value to data type int in SQL

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

Upvotes: 0

Views: 1428

Answers (2)

Radu
Radu

Reputation: 1031

This should work, you don't need the casts to get the diff in seconds ...

select datediff (SECOND,start, stop) from test

Just tot test it ...

select 1 
where ((select datediff (SECOND,start, stop) from test) > 5)

datediff() query: enter image description here test table:

enter image description here

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

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.

Upvotes: 1

Related Questions