Jeff, for what it's worth, I had a requirement to do a query a few weeks ago that only displayed the time part (and formatted as AM/PM too). The spec required that the date part NOT be displayed.

The output was for a golf tournament signup schedule and the end-user needed a column to display tee times. These were actual times of course and not durations. The day date was on the page showing the tee times for that day. I'd think this might apply as well to any scheduling situation such as at a doctor's office, etc.

In my case, the "date" values coming from the application source for that column were in the form "10:35","14:22", etc. And the user's SQL version was 2005 so the TIME datatype was unavailable! I discovered though that the DATETIME datatype accepts input such as "14:22" as is and merely converts it to "1900-01-01 14:22:00.000". I considered trying to put in the actual date, but since there was this implicit conversion and the day date was stored in another date column (bad design? yeah!), I just used it as is.

Examples:

--SQL 2005

DECLARE @TeeTime DATETIME

SET @TeeTime = '14:22'

SELECT @TeeTime AS TeeTime

--> Output: 1900-01-01 14:22:00.000

--SQL 2008

DECLARE @TeeTime1 TIME

SET @TeeTime1 = '14:22'

SELECT @TeeTime1 AS TeeTime

--> Output: 14:22:00.0000000

Getting durations:

DECLARE @Time1 TIME

SET @Time1 = '14:22'

DECLARE @Time2 TIME

SET @Time2 = '14:28'

SELECT @Time2-@Time1 AS TimeDuration

--> Output: error: 'Operand data type time is invalid for subtract operator.'

DECLARE @Time3 DATETIME

SET @Time3 = '14:22'

DECLARE @Time4 DATETIME

SET @Time4 = '14:28'

SELECT CAST(@Time4-@Time3 AS TIME) AS TimeDuration

--> Output: 00:06:00.0000000

Adding time to get a new time:

DECLARE

@Time5 TIME

,@TimeInterval1 TIME

SET @Time5 = '14:22'

SET @TimeInterval1 = '00:06'

SELECT @Time5+@TimeInterval1 AS NextTime

--> Output: error: 'Operand data type time is invalid for add operator.'

DECLARE

@Time6 DATETIME

,@TimeInterval2 DATETIME

SET @Time6 = '14:22'

SET @TimeInterval2 = '00:06'

SELECT CAST(@Time6+@TimeInterval2 AS TIME) AS NextTime

--> Output: 14:28:00.0000000