Difference between hours and minutes days in Sql Server?

Asked

Viewed 1,067 times

1

I got two columns like datetime. I have tried in many ways to catch the difference between them, but the number of days is always 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

It is always taken into account the change of day. In the example above despite not having completed 24h, the result of DATEDIFF already returns 1 day.

sql sql-server tsql

1 answer

1

I don’t know if I understand this right, 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 in full, for example:

Passed by 0 days, 17 hours, 1 minute and 1 second since the last check.

  • 1

    Leandro, it worked perfectly!

  • 1

    Leandro, it worked perfectly! The problem is that I was making the DATADIFF using 'DAY' to find the days difference. From there, only the passage from one day to the next was taken into account. Ex: Data1: 2018-01-01 Data2: 2018-01-02, always gave difference of 1 day, not taking into account the hours.

  • 1

    One question I had was about this part CONVERT(VARCHAR,DATEDIFF(SECOND, excur_start, excur_end) / 60% 60). Could you explain this expression to me? " / 60% 60"

  • 1

    Hi Leandro! I managed to decipher the expression! Math thing! rsrs

  • This % is the MOD, brings as a result the "rest" of the division.

Browser other questions tagged sql sql-server tsql

You are not signed in. Login or sign up in order to post.