Matthew Matthew - 1 year ago 66
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
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:

2014-10-13 17:30:00.000

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
part of an

Answer Source

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.