Difference of exact hours between Date A and Date B

Navigation
1
$qry = "select 

                CONVERT(VARCHAR(20),fechaSalida,108) AS fechaSalida,
                CONVERT(VARCHAR(20),fechaEntrada,108) AS fechaEntrada, 
                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 answers

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

    Resolved in your query: 

    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 ...
        
    answered by 18.09.2018 / 23:32
    source
    0

    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

        
    answered by 18.09.2018 в 23:26
    Access other properties of the same object Copy CANVAS in image format