Matthew - 8 months ago 33

SQL Question

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`

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

`UPDATE`

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`

.