How to convert Time to Decimal in SQL with an integer

 How to convert Time to Decimal in SQL with an integer

I am having a couple of issues converting time to decimal...

Essentially I have used a DATEDIFF to identify the hours and minutes between two dates and times. This produces a result of 5.45, however I would like to get the result as 5.75.

select RTRIM(DATEDIFF(second, startdate, enddate)/3600)+'.'
+ RIGHT('0'+RTRIM((DATEDIFF(second, startdate, enddate) % 3600)/60),2)
from time

I have tried a few things but I believe the issue is that this is not an integer and that's why i cant convert the minutes.

Asked By:Withdalot

||

Source

Answer #1:

Get the time difference in minutes, and divide by 60, this will place hours before the decimal separator and minutes after:

datediff(minute, startdate, enddate) / 60.0

Answered By:Guffa

Answer #2:

Select DATEDIFF(second,  startdate, enddate)/3600.0

Answered By:bummi

Answer #3:

As mentioned in this answer, "Take the DateDiff in seconds instead, and then divide by 86400.0. The decimal point is required."

Answered By:Alex Gordon

Nguồn: https://www.py4u.net/discuss/836488