Dim sql As String = "SELECT a.*,
CAST(DATEDIFF (minute, a.fechaEntrada ,a.fechaSalida)/ 60.0 as decimal (18,2)) %24 as horasTrabajadas
from tblasistencia a
WHERE a.idClave = '" & empno & "' and a.fechaEntrada BETWEEN '" & fechaInicio & "' AND '" & fechaFin & "' "
How can I improve the query, so that when doing the sum of hours with the DATEDIFF function, I return the calculations more precisely, that is, I want to make the data 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
In the following way, the exact hours come out but I can not find the way to concatenate and add them to the query.
((DateDiff(Second, a.fechaEntrada, a.fechaSalida)%86400)/3600) + ':'+
(((DateDiff(Second,a.fechaEntrada, a.fechaSalida)%86400)%3600)/60) horasTrabajadas