Search code examples
sql-server-2014

How to calculate time difference more than 24 hours in sql server 2014

Finding the difference of time which is more than 24 hours. As of now I am trying with time variable but it can show difference less than 24 hours.

Solution

  • Suppose you want to know the time difference between
    2017-01-01 13:45:00 and 2017-01-02 16:30:00 - in a resolution of minutes - so the desired result would be 26:45 (26 hours and 45 minutes), you can do something like this:

    DECLARE @From datetime = '2017-01-01 13:45:00',
        @To datetime = '2017-01-02 16:30:00'

DECLARE @Diff int

SELECT @Diff = DATEDIFF(MINUTE, @From, @To)

SELECT  
        CAST((@Diff - (@Diff % 60)) / 60 as varchar(5)) -- Hours
        + ':' + 
        RIGHT('00' + 
              CAST(@Diff % 60 as varchar(2)) -- minutes
              , 2)

    Result:

    26:45

    Note: right('00' + <varchar value>, 2) will add a leading zero incase the <varchar value> is only one char long.