CODEWITHSUNDEEP
sqlsql-servert-sql
goxarad784
goxarad784

Reputation: 435

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?

Upvotes: 0

Views: 72

Answers (2)

Dale K
Dale K

Reputation: 27202

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269513

SQL Server does integer division. So, you need to divide by a number with a decimal point:

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

You could also cast the DATEDIFF() to a numeric or floating point value for this purpose.

Upvotes: 1

Related Questions