How best to compute and store a difference in time values? (SQL2008R2)

Giganews Newsgroups
Subject: How best to compute and store a difference in time values? (SQL2008R2)
Posted by:  new DBA in '09 (ericbrag…@gmail.com)
Date: Wed, 1 Dec 2010

I ANSWERED MY OWN QUESTION IN THIS POST, BUT AM STILL OPEN TO ADVICE!

Hello,

My table has StartTime and EndTime fields, both being TIME data
types.  A report needs to show the length of time between the two
values.  How is a timespan between two TIME values computed?

I know how to do it with two DATETIME values: I convert them to
floats, then subtract the earlier from the later, then convert the
returned value back to a datetime, like so:

DECLARE @x datetime, @y datetime
SET @x = '2010-12-01 07:40:03.555'
SET @y = '2010-12-01 07:49:14:666'
SELECT cast(@y - @x AS float) AS
[DifferenceAsFloat]    --0.00637858796296296
, cast(cast(@y - @x AS float) AS datetime) AS
[FloatDifferenceAsDateTime] --1900-01-01 00:09:11.110

(Of course, I'll need to ignore the date value in
FloatDifferenceAsDateTime, because 9 minutes, 11.110 seconds is a
difference, not an actual point in time.)

Is converting to datetime values before subtracting the best way to do
it?  I find the below works, but I'll need to ignore the date portion.

DECLARE @x time, @y time
SELECT @x = GETDATE()
SELECT @y = DATEADD(hour, 2, dateadd(minute, 32, dateadd(second, 19,
dateadd(millisecond, 543, @x))))
SELECT CAST(@y AS datetime) - CAST(@x AS datetime)

--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==--==
Actually, I just answered my own question:

DECLARE @x time, @y time
SELECT @x = GETDATE()
SELECT @y = DATEADD(hour, 2, dateadd(minute, 32, dateadd(second, 19,
dateadd(millisecond, 543, @x))))
SELECT    CAST(CAST(@y AS datetime) - CAST(@x AS datetime) AS Time(3))

Replies