Calculate Time Difference in Minutes in SQL Server

Calculate time difference in minutes in SQL Server

The following works as expected:

SELECT  Diff = CASE DATEDIFF(HOUR, StartTime, EndTime)
                    WHEN 0 THEN CAST(DATEDIFF(MINUTE, StartTime, EndTime) AS VARCHAR(10))
                    ELSE CAST(60 - DATEPART(MINUTE, StartTime) AS VARCHAR(10)) +
                        REPLICATE(',60', DATEDIFF(HOUR, StartTime, EndTime) - 1) + 
                        + ',' + CAST(DATEPART(MINUTE, EndTime) AS VARCHAR(10))
                END
FROM    (VALUES 
            (CAST('11:15' AS TIME), CAST('13:15' AS TIME)),
            (CAST('10:45' AS TIME), CAST('18:59' AS TIME)),
            (CAST('10:45' AS TIME), CAST('11:59' AS TIME))
        ) t (StartTime, EndTime);

To get 24 columns, you could use 24 case expressions, something like:

SELECT  [0] = CASE WHEN DATEDIFF(HOUR, StartTime, EndTime) = 0
                        THEN DATEDIFF(MINUTE, StartTime, EndTime)
                    ELSE 60 - DATEPART(MINUTE, StartTime)
                END,
        [1] = CASE WHEN DATEDIFF(HOUR, StartTime, EndTime) = 1 
                        THEN DATEPART(MINUTE, EndTime)
                    WHEN DATEDIFF(HOUR, StartTime, EndTime) > 1 THEN 60
                END,
        [2] = CASE WHEN DATEDIFF(HOUR, StartTime, EndTime) = 2
                        THEN DATEPART(MINUTE, EndTime)
                    WHEN DATEDIFF(HOUR, StartTime, EndTime) > 2 THEN 60
                END -- ETC
FROM    (VALUES 
            (CAST('11:15' AS TIME), CAST('13:15' AS TIME)),
            (CAST('10:45' AS TIME), CAST('18:59' AS TIME)),
            (CAST('10:45' AS TIME), CAST('11:59' AS TIME))
        ) t (StartTime, EndTime);

The following also works, and may end up shorter than repeating the same case expression over and over:

WITH Numbers (Number) AS
(   SELECT  ROW_NUMBER() OVER(ORDER BY t1.N) - 1
    FROM    (VALUES (1), (1), (1), (1), (1), (1)) AS t1 (N)
            CROSS JOIN (VALUES (1), (1), (1), (1)) AS t2 (N)
), YourData AS
(   SELECT  StartTime, EndTime
    FROM    (VALUES 
                (CAST('11:15' AS TIME), CAST('13:15' AS TIME)),
                (CAST('09:45' AS TIME), CAST('18:59' AS TIME)),
                (CAST('10:45' AS TIME), CAST('11:59' AS TIME))
            ) AS t (StartTime, EndTime)
), PivotData AS
(   SELECT  t.StartTime,
            t.EndTime,
            n.Number,
            MinuteDiff = CASE WHEN n.Number = 0 AND DATEDIFF(HOUR, StartTime, EndTime) = 0 THEN DATEDIFF(MINUTE, StartTime, EndTime)
                                WHEN n.Number = 0 THEN 60 - DATEPART(MINUTE, StartTime)
                                WHEN DATEDIFF(HOUR, t.StartTime, t.EndTime) <= n.Number THEN DATEPART(MINUTE, EndTime)
                                ELSE 60
                            END
    FROM    YourData AS t
            INNER JOIN Numbers AS n
                ON n.Number <= DATEDIFF(HOUR, StartTime, EndTime)
)
SELECT  *
FROM    PivotData AS d
        PIVOT 
        (   MAX(MinuteDiff)
            FOR Number IN 
            (   [0], [1], [2], [3], [4], [5], 
                [6], [7], [8], [9], [10], [11],
                [12], [13], [14], [15], [16], [17], 
                [18], [19], [20], [21], [22], [23]
            ) 
        ) AS pvt;

It works by joining to a table of 24 numbers, so the case expression doesn't need to be repeated, then rolling these 24 numbers back up into columns using PIVOT

Calculate time difference in minutes in SQL Server 2008

Datediff function will do the trick

select id,datediff(minute,min(timedif),max(timedif) ) AS time from support 
where in_organization = 'ORGANIZATION_NAME_1' or from_organization = 'ORGANIZATION_NAME_1'  
group by id ;

My Output:

    |id     |time
1   |22907  |2
2   |23289  |120

Let tme know in case of any queries.

Calculating time difference when times are cross midnight

Just use a case expression:

(case when s.start_ < s.end_
      then datediff(hour, S.Start_, S.End_)
      else datediff(hour, S.End_, s.Start_)
 end)

Dividing the minutes by 60 and ignoring the remainder is the same as counting the hours.

Time Difference between two time columns in sql server

Just add 1440 minutes (number of minutes in 24 hours) if start time is greater than end time:

select
    start_time_time,
    end_time_time,
    datediff(minute, start_time_time, end_time_time) + iif(start_time_time <= end_time_time, 0, 1440) as diff
from (
    select
    convert(time(0), dty_act_start_time, 114) as start_time_time,
    convert(time(0), dty_act_end_time, 114) as end_time_time
    from (values
        ('09:00am', '10:00pm'),
        ('10:00pm', '09:00am')
    ) tests(dty_act_start_time, dty_act_end_time)
) x

Result:

start_time_time | end_time_time | diff
09:00:00        | 22:00:00      | 780
22:00:00        | 09:00:00      | 660

SQL Calculate time difference between two times and show the hours

Just another option

Example

Select TimeCardID
      ,EmpNumber
      ,CheckIn  = min(TransDateTime)
      ,CheckOut = max(TransDateTime)
      ,TotalHours = DateDiff(MINUTE,min(TransDateTime),max(TransDateTime)) / 60.0
 From ( 
        Select *
              ,Grp = (1+row_number() over (partition by EmpNumber,TimeCardID Order by TransDateTime)) / 2
         From YourTable
      ) A
 Group By TimeCardID,EmpNumber,Grp

Returns

Sample Image

Time difference with hours, minutes and seconds in SQL Server

This should work for you -

DECLARE @STOP_TIME DATETIME = '2016-05-10 03:31:00.000',
        @Start_Time DATETIME = '2016-05-10 02:25:34.000'
SELECT 
     RIGHT('0' + CAST(DATEDIFF(S, @Start_Time, @STOP_TIME) / 3600 AS VARCHAR(2)),2) + ':'
   + RIGHT('0' + CAST(DATEDIFF(S, @Start_Time, @STOP_TIME) % 3600/60 AS VARCHAR(2)),2) + ':'
   + RIGHT('0' + CAST(DATEDIFF(S, @Start_Time, @STOP_TIME) % 60 AS VARCHAR(2)),2)

How to calculate time difference in T-SQL

You can do it this way:

select *, convert(time, convert(datetime, timeto) - convert(datetime, timefrom)) 
from table1

This will convert the times to datetime for day 0 (1.1.1900) and then do the calculation and in case the timeto is smaller it will get to previous day, but convert to time will get the time part from it.

Example in SQL Fiddle