Difference between days and minutes on Sql Server?

1

I have two columns of type datetime . I have tried in many ways to get the difference between them, but the number of days always goes incorrect.

Example:

Data1: 2018-01-17 17:00:00
Data2: 2018-01-18 10:00:00

Resultado obtido:  01 17:00:00
Resultado esperado: 00 17:00:00

The day change is always taken into account. In the above example even though you did not complete 24h, the result of DATEDIFF already returns 1 day.

    
asked by anonymous 18.01.2018 / 01:37

1 answer

1

I do not know if I understand correctly, but I think this is what you want

select CAST( '2018-01-01 17:00:01' AS DATETIME ) AS data_um,
        CAST( '2018-01-02 10:01:02' AS DATETIME ) AS data_dois,
        DATEDIFF( SECOND, CAST( '2018-01-01 17:00:01' AS DATETIME ),CAST( '2018-01-02 10:01:02' AS DATETIME ) ) / 60/ 60/ 24 AS difer_dias,
        DATEDIFF( SECOND, CAST( '2018-01-01 17:00:01' AS DATETIME ),CAST( '2018-01-02 10:01:02' AS DATETIME ) ) / 60/ 60 AS difer_horas,
        DATEDIFF( SECOND, CAST( '2018-01-01 17:00:01' AS DATETIME ),CAST( '2018-01-02 10:01:02' AS DATETIME ) ) / 60% 60 AS difer_minutos,
        DATEDIFF( SECOND, CAST( '2018-01-01 17:00:01' AS DATETIME ),CAST( '2018-01-02 10:01:02' AS DATETIME ) ) % 60 AS difer_segundos

It will show the amount of seconds, minutes, hours and days, in a way that can be used extensively, for example:

If you have passed 0 days, 17 hours, 1 minute and 1 second since the last check.

    
18.01.2018 / 02:57