Time is no use for recording Time
I was asked by a client about the new Time datatype in
SQL 2008. They wanted to store the duration of some work so they could use the
nice date time functions to get the hours, minutes, seconds etc.
However you can't use the Time date type for that, because it can only hold
time up to midnight after that it roles around to 0. Not much use if something
takes longer than 24 hrs to run.
If you would like to have such a feature then vote for the
reccomendation for an interval data type. https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=432281
If you can't wait until fro a nother 3 or 4 years then you could cheat.
If you use the datetime data type then you can add these together and get the
behaviour you want. So you can get 04:53 and add it to 20:52.
To find the hours, months etc you perform a datedfif with 0. like this
declare @time
datetime
set @time
= cast('4:53' as datetime ) + cast('20:52' as datetime)
select DATEDIFF (minute,0,@time)
NOTE: You can't do this type of calculation with the new date and
time data types you have to use the date add functions. This IMHO is one of
the poor aspects of the new data types