Ian Fafard Ian Fafard - 1 year ago 63
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.


Answer Source

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.