question

VDT-7677 avatar image
0 Votes"
VDT-7677 asked TomCooper-6989 answered

Calculating Elapsed Time From Start & End Time

Hi,

Running SQL Server 2008 R2. I have the following sample table:

 CREATE TABLE [dbo].[Test]
     (
     [Test_ID] [int] NOT NULL,
     [Start_Time] [char](5) NULL,
     [End_Time] [char](5) NULL,
     [Elapsed_Time] [char](5) NULL,
     CONSTRAINT
         [PK_Test_Test_ID] PRIMARY KEY CLUSTERED ([Test_ID] ASC)
     WITH
         (
  PAD_INDEX = OFF,
  STATISTICS_NORECOMPUTE = OFF,
  IGNORE_DUP_KEY = OFF,
  ALLOW_ROW_LOCKS = ON,
  ALLOW_PAGE_LOCKS = ON
  )
     ON [PRIMARY]
     )
 ON [PRIMARY]
 GO

Start and End Times are stored in 'hh:nn' format.

How could I use T-SQL to calculate the elapsed time between [Start_Time] and [End_Time], stored in 'hh:nn' format? [Elapsed_Time] will never exceed 24:00 but there may be occasions where [Start_Time] could be 23:45 and [End_Time] could be 00:05. Under these conditions [Elapsed_Time] would need to be 00:20.

Thanks in advance for any assistance!

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomCooper-6989 avatar image
0 Votes"
TomCooper-6989 answered VDT-7677 commented
 ;With cte As
 (Select TestID, Elapsed_Time, DateDiff(minute, Start_Time, End_Time) + Case When Start_Time > End_Time Then 24*60 Else 0 End As TimeInMinutes
 From dbo.Test)
 Update cte
 Set Elapsed_Time = Convert(char(5), DateAdd(minute, TimeInMinutes, '19000101'), 8)
 From cte;

Tom

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Tom,

Thanks for the quick reply. One thing: when I have a [Start_Time] of 06:00 and [End_Time] of 06:00 the calculation should work out to 24:00, but instead gives a result of 00:00. How would I modify the above query to account for this scenario?

Thanks!

0 Votes 0 ·
TomCooper-6989 avatar image
0 Votes"
TomCooper-6989 answered

Then you want to handle the case when TimeInMinutes = 0 as a special case, so

 ;With cte As
 (Select TestID, Elapsed_Time, DateDiff(minute, Start_Time, End_Time) + Case When Start_Time > End_Time Then 24*60 Else 0 End As TimeInMinutes
 From dbo.Test)
 Update cte
 Set Elapsed_Time = Case When TimeInMinutes = 0 Then '24:00'
     Else Convert(char(5), DateAdd(minute, TimeInMinutes, '19000101'), 8) End;

Tom

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.