SQL Server - calculate elapsed time between two datetime stamps in HH MM SS format

Question

I have a SQL Server table that has a  Time  column   The table is a log table the houses status messages and timestamps for each message   The log table is inserted into via a batch file  There is an ID column that groups rows together   Each time the batch file runs it initializes the ID and writes records   What I need to do is get the elapsed time from the first record in an ID set to the last record of the same ID set   I started toying with select Max Time  - Min Time  from logTable where id    but couldn t figure out how to format it correctly   I need it in HH MM SS

User · Answer

UPDATED   Correctly calculate a timespan in SQL Server  even if more than 24 hours   -- Setup test data declare  minDate datetime    2012-12-12 20 16 47 160  declare  maxDate datetime    2012-12-13 15 10 12 050   -- Get timespan in hh mi ss select cast           cast cast  maxDate as float  - cast  minDate as float  as int    24     hours over 24              datepart hh   maxDate -  minDate     hours            as varchar 10               right  0    cast datepart mi   maxDate -  minDate  as varchar 2    2     minutes                right  0    cast datepart ss   maxDate -  minDate  as varchar 2    2     seconds     -- Returns 18 53 24   Edge cases that show inaccuracy are especially welcome

User · Answer

Hope this helps you in getting the exact time between two time stamps  Create PROC TimeDurationbetween2times  iTime as time  oTime as time   As   Begin    DECLARE  Dh int   Dm int   Ds int   Im int   Om int   Is int  Os int       SET  Im DATEPART MI  iTime    SET  Om DATEPART MI  oTime    SET  Is DATEPART SS  iTime    SET  Os DATEPART SS  oTime     SET  Dh DATEDIFF hh  iTime  oTime    SET  Dm   DATEDIFF mi  iTime  oTime    SET  Ds   DATEDIFF ss  iTime  oTime     DECLARE  HH as int   MI as int   SS as int    if  Im gt  Om    begin   SET  Dh  Dh-1   end   if  Is gt  Os    begin   SET  Dm  Dm-1   end    SET  HH    Dh   SET  MI    Dm- 60  HH    SET  SS    Ds- 60  Dm     DECLARE  hrsWkd as varchar 8            SET  hrsWkd   cast  HH as char 2       cast  MI as char 2       cast  SS as char 2              select  hrsWkd as TimeDuration     End

User · Answer

The best and simple way   Convert varchar   EndTime  -  StartTime   108    Just like Anri noted

User · Answer

select convert varchar  Max Time  - Min Time    108  from logTable where id

User · Answer

SQL Server doesn t support the SQL standard interval data type  Your best bet is to calculate the difference in seconds  and use a function to format the result  The native function CONVERT   might appear to work fine as long as your interval is less than 24 hours  But CONVERT   isn t a good solution for this    create table test     id integer not null    ts datetime not null       insert into test values  1   2012-01-01 08 00    insert into test values  1   2012-01-01 09 00    insert into test values  1   2012-01-01 08 30    insert into test values  2   2012-01-01 08 30    insert into test values  2   2012-01-01 10 30    insert into test values  2   2012-01-01 09 00    insert into test values  3   2012-01-01 09 00    insert into test values  3   2012-01-02 12 00      Values were chosen in such a way that for    id   1  elapsed time is 1 hour id   2  elapsed time is 2 hours  and id   3  elapsed time is 3 hours    This SELECT statement includes one column that calculates seconds  and one that uses CONVERT   with subtraction   select t id         min ts  start time         max ts  end time         datediff second  min ts  max ts   elapsed sec         convert varchar  max ts  - min ts   108  do not use from test t group by t id   ID  START TIME                 END TIME                   ELAPSED SEC  DO NOT USE 1   January  01 2012 08 00 00  January  01 2012 09 00 00  3600         01 00 00 2   January  01 2012 08 30 00  January  01 2012 10 30 00  7200         02 00 00 3   January  01 2012 09 00 00  January  02 2012 12 00 00  97200        03 00 00   Note the misleading  03 00 00  for the 27-hour difference on id number 3   Function to format elapsed time in SQL Server

User · Answer

DECLARE  EndTime AS DATETIME   StartTime AS DATETIME  SELECT  StartTime    2013-03-08 08 00 00    EndTime    2013-03-08 08 30 00   SELECT CAST  EndTime -  StartTime AS TIME    Result  00 30 00 0000000  Format result as you see fit

User · Answer

See if this helps  I can set variables for Elapsed Days  Hours  Minutes  Seconds  You can format this to your liking or include in a user defined function   Note  Don t use DateDiff hh  Date1  Date2    It is not reliable   It rounds in unpredictable ways  Given two dates     Sample Dates  two days  three hours  10 minutes  30 seconds difference   declare  Date1 datetime    2013-03-08 08 00 00  declare  Date2 datetime    2013-03-10 11 10 30  declare  Days decimal declare  Hours decimal declare  Minutes decimal declare  Seconds decimal  select  Days   DATEDIFF ss  Date1  Date2  60 60 24 --Days declare  RemainderDate as datetime    Date2 -  Days select  Hours   datediff ss   Date1   RemainderDate  60 60 --Hours set  RemainderDate    RemainderDate -   Hours 24 0  select  Minutes   datediff ss   Date1   RemainderDate  60 --Minutes set  RemainderDate    RemainderDate -   Minutes 24 0 60  select  Seconds   DATEDIFF SS   Date1   RemainderDate      select  Days as ElapsedDays   Hours as ElapsedHours   Minutes as ElapsedMinutes   Seconds as ElapsedSeconds

User · Answer

Use the DATEDIFF to return value in milliseconds  seconds  minutes  hours       DATEDIFF interval  date1  date2   interval REQUIRED - The time date part to return  Can be one of the following values   year  yyyy  yy   Year quarter  qq  q   Quarter month  mm  m   month dayofyear   Day of the year day  dy  y   Day week  ww  wk   Week weekday  dw  w   Weekday hour  hh   hour minute  mi  n   Minute second  ss  s   Second millisecond  ms   Millisecond   date1  date2 REQUIRED - The two dates to calculate the difference between

[sql-server-2008] SQL Server - calculate elapsed time between two datetime stamps in HH:MM:SS format

Examples related to sql-server-2008

Examples related to datetime

Examples related to elapsedtime