Calculating Elapsed Time From Start & End Time

Question

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!

Answer 1

;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

Answer 2

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