Accurate DateDiff

I have 2 time fields in SQL. I want to calculate the difference between start time and end time in hours.

From 7 AM in the morning to 7:30 PM it would be 12.5 hours but the datediff function returns 12 hours because it returns an integer.

I also tried converting it to seconds:

SELECT (DATEDIFF(HOUR,'07:00:00.0000000', '19:30:00.0000000')); 

SELECT (DATEDIFF(SECOND,'07:00:00.0000000', '19:30:00.0000000'))/3600;

SELECT (DATEDIFF(MILLISECOND,'07:00:00.0000000', '19:30:00.0000000'))/3600000;

All of these return 12 hours which is not accurate. Is there a way to do a difference between 2 time values in SQL accurately?

Answer

You need to force a decimal result instead of an int result – which you can do by changing 3600 to 3600.00.

SELECT (DATEDIFF(SECOND,'07:00:00.0000000', '19:30:00.0000000'))/3600.00;

Knowing the datatype, precision and scale of the result of a mathematical operation is unfortunately not straight forward in SQL Server. I recommend taking a read of this question and its associated links for better understanding.