Matthew - 1 year ago 117
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.

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.
`````` 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`.