Calculate time difference in minutes in SQL Server

Question

I need the time difference between two times in minutes  I am having the start time and end time as shown below   start time   End Time     11 15 00     13 15 00     10 45 00     18 59 00   I need the output for first row as 45 60 15 which corresponds to the time difference between 11 15 and 12 00  12 00 and 13 00  13 00 and 13 15 respectively

User · Answer

You can use DATEDIFF it is a built-in function  and    for scale calculation  and CONCAT for make result to only one column  select CONCAT  Month    MonthDiff   Days      DayDiff   Minutes    MinuteDiff   Seconds    SecondDiff  as T  from   SELECT DATEDIFF MONTH   2017-10-15 19 39 47     2017-12-31 23 59 59     12 as MonthDiff          DATEDIFF DAY   2017-10-15 19 39 47     2017-12-31 23 59 59     30 as DayDiff          DATEDIFF HOUR   2017-10-15 19 39 47     2017-12-31 23 59 59     24 as HourDiff          DATEDIFF MINUTE   2017-10-15 19 39 47     2017-12-31 23 59 59     60 AS MinuteDiff          DATEDIFF SECOND   2017-10-15 19 39 47     2017-12-31 23 59 59     60 AS SecondDiff  tbl

User · Answer

Apart from the DATEDIFF you can also use the TIMEDIFF function or the TIMESTAMPDIFF   EXAMPLE  SET  date1    2010-10-11 12 15 35    date2    2010-10-10 00 00 00    SELECT  TIMEDIFF  date1   date2  AS  TIMEDIFF   TIMESTAMPDIFF hour   date1   date2  AS  Hours   TIMESTAMPDIFF minute   date1   date2  AS  Minutes   TIMESTAMPDIFF second   date1   date2  AS  Seconds     RESULTS   TIMEDIFF   36 15 35 Hours   -36 Minutes   -2175 Seconds   -130535

User · Answer

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   gt  1 THEN 60                 END           2    CASE WHEN DATEDIFF HOUR  StartTime  EndTime    2                         THEN DATEPART MINUTE  EndTime                      WHEN DATEDIFF HOUR  StartTime  EndTime   gt  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   lt   n Number THEN DATEPART MINUTE  EndTime                                  ELSE 60                             END     FROM    YourData AS t             INNER JOIN Numbers AS n                 ON n Number  lt   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

User · Answer

Please try as below to get the time difference in hh mm ss format Select StartTime  EndTime  CAST  EndTime - StartTime  as time 0    TotalTime  from  TableName

User · Answer

Use DateDiff with MINUTE difference    SELECT DATEDIFF MINUTE   11 10 10     11 20 00   AS MinuteDiff   Query that may help you    SELECT StartTime  EndTime  DATEDIFF MINUTE  StartTime   EndTime  AS MinuteDiff  FROM TableName

User · Answer

Try this     select starttime endtime  case   when DATEDIFF minute starttime endtime   lt  60  then DATEDIFF minute starttime endtime     when DATEDIFF minute starttime endtime   gt   60   then  60    cast   cast DATEDIFF minute starttime endtime  as int  -60  as nvarchar 50    end from TestTable123416   All You need is DateDiff

[sql] Calculate time difference in minutes in SQL Server

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to datetime

Examples related to difference