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.
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.