bmsqldev bmsqldev - 4 months ago 9
SQL Question

Add the time values in SQL Server

We have an hour table in our application which stores the working hours for each associate. It has hour values as follow

0.30 ,
0.30 ,
1.10


0.30
indicates 30 minutes and 1.10 indicates 1 hour 10 minutes. So when I calculate the sum of hours I got 1.7, but I need to get 1.3 (I need to convert
1.10
to
0.70
).

How to achieve this?

Answer

you can use some math calculation to achieve what you want ..

SELECT workHours - FLOOR(workHours) + FLOOR(workHours)*0.60

sample value:

SELECT 1.10 - FLOOR(1.10) + FLOOR(1.10)* 0.60

result: 0.70