# Difference of exact hours between Date A and Date B

1
``````\$qry = "select

CONVERT(VARCHAR(20),fechaSalida,108) AS fechaSalida,
CAST(DATEDIFF(minute, fechaEntrada, fechaSalida)/60.0 as decimal (18,2) ) %24 AS sumaHoras
from tblasistencia where idclave = '".\$empno."' and
fechaEntrada BETWEEN CONVERT(DATETIME, '".\$fechainicio." 00:00:00',103) and
CONVERT(DATETIME, '".\$fechainicio." 23:59:59',103)";
``````

How can I improve the query, so that when doing the sum of hours with the function DATEDIFF I return more exact calculations, that is, I want to make more user friendly, reading the data.

``````Caso 1
21:00:50    06:02:51    9.03
``````

Multiply 0.03 * 60 so that it leaves 1.8 minutes apart, that is 9 hours and 1.8 minutes.

Current result

`````` Hora A      Hora B     Suma
21:00:50    06:02:51    9.03
21:09:40    06:26:16    9.28
21:12:06    07:22:12    10.17
21:16:45    06:26:16    9.17
21:27:36    06:02:40    8.58
21:21:10    06:09:49    8.8
21:12:13    06:09:48    8.95
21:08:34    06:02:32    8.9
``````

asked by Noel L 18.09.2018 в 22:34
source

2

A general way to solve it is by calculating the difference in a minimum magnitude, for example in seconds, and basically what we will do is:

• The quotient of the divided seconds 86400 is the days (One day has 86400 sec.)
• The quotient of the rest of (1) divided 3600 will be the hours
• The quotient of the rest of (2) divided 60 will be the minutes
• The final rest will be the seconds
• Conceptually it would be something like this:

``````DECLARE @Diff       INT
DECLARE @From       DATETIME
DECLARE @To         DATETIME

SELECT  @From   = '1900-01-01 21:00:50'
SELECT  @To     = '1900-01-02 06:02:51'
SELECT  @Diff   = DATEDIFF(SECOND,@From,@To)

select  convert(varchar(10), (@Diff/86400)) + ':' +                        -- dia
convert(varchar(10), ((@Diff%86400)/3600)) + ':'+                  -- hora
convert(varchar(10), (((@Diff%86400)%3600)/60)) + ':'+             -- min
convert(varchar(10), (((@Diff%86400)%3600)%60)) as 'DD:HH:MM:SS'   -- seg
``````

Exit (9 hours two minutes 1 second):

``````DD:HH:MM:SS
0:9:2:1
``````

``````select  convert(varchar(10), (DATEDIFF(SECOND, fechaEntrada, fechaSalida)/86400)) + ':' +                        -- dia
convert(varchar(10), ((DATEDIFF(SECOND, fechaEntrada, fechaSalida)%86400)/3600)) + ':'+                  -- hora
convert(varchar(10), (((DATEDIFF(SECOND, fechaEntrada, fechaSalida)%86400)%3600)/60)) + ':'+             -- min
convert(varchar(10), (((DATEDIFF(SECOND, fechaEntrada, fechaSalida)%86400)%3600)%60)) as 'DD:HH:MM:SS'   -- seg
from ...
``````

Instead of using `(DATEDIFF(minute, fechaEntrada, fechaSalida)` use `(DATEDIFF(time, fechaEntrada, fechaSalida)` because what you want to get is the exact time with your minutes and seconds, you do not have to convert them to decimal convert them to type `TIME`