Matthew Matthew - 4 months ago 20
SQL Question

Getting Hour and minute in from DATEDIFF in decmial format, SQL

In my SQL table I have 3 coulmns:

StartDateTime (DATETIME),
EndDateTime (DATETIME),
TimeWorked (DECIMAL(9,2))


I am trying to fill the TimeWorked column with the total hours and minutes between the start and end time. If I use

DATEDIFF(HOUR, StartDateTime, EndDateTime)


I get the hours rounded down, so I thought I would use

DATEDIFF(MINUTE, StartDateTime, EndDateTime) / 60


but on a time of 90 minutes I am given 1.00 hours. I did some searching and found most places suggest converting the number so I tried converting just the result of the
DATEDIFF
and the entire math equation like

CONVERT(decimal(9,2),(DATEDIFF(MINUTE, StartDateTime, EndDateTime)) / 60);
CONVERT(decimal(9,2),(DATEDIFF(MINUTE, StartDateTime, EndDateTime) / 60));


but the results are still being rounded down to the nearest hour. The times I am using to test are:

StartDateTime
2014-10-13 17:30:00.000

EndDateTime
2014-10-13 19:00:00.000


What is the best way to get the number or hours and minutes in a decimal format (i.e. 1.5 would be the correct result for this time)? If it makes any difference this is being in the
SET
part of an
UPDATE
statement.

Answer

You can use DATEDIFF(MINUTE, StartDateTime, EndDateTime) / 60.0 to avoid integer division and get the result you want.

This works because 60.0 is interpreted as a literal of the decimal rather than integer type.

The problem with your approach here

 CONVERT(decimal(9,2),(DATEDIFF(MINUTE, StartDateTime, EndDateTime) / 60));

Is that the integer division already occurs before the cast. So you are just converting the integer 1 to a decimal.