Ian Fafard Ian Fafard - 6 months ago 14x
SQL Question

datediff() of 2 timestamps that contain decimal values

I've been stumbling with this issue for a couple days now, and cannot seem to figure out why, when my getdate() insert into the columns are providing a millisecond decimal to the military time format, I still cannot seem to be able to pull a decimal format datediff() result. Does it have to do with the engine not recognizing the decimal due to the surrounding '' characters?

When I use:

select datediff(s,'2013-06-01 21:59:59.141','2013-06-01 23:59:59.997')

It returns:


And when I use:

select cast(datediff(s,'2013-06-01 21:59:59.141','2013-06-01 23:59:59.997') as float);

It returns:


I am at a loss as to what I am missing in order to result in a decimal value.



If you are trying to get the milliseconds of the difference, and you want to convert the units to seconds, you can try using something like the following:

SELECT DATEDIFF(MS,'2013-06-01 21:59:59.141','2013-06-01 23:59:59.997') / 1000.0

That'll produce: 7200.856000.