# question

## 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
(
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!

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

`````` ;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

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!

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

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