Jeff Moden (8/2/2014)
Here's what I'd use to solve this problem on my systems because it's short and accurate with the accuracy limits of DATETIME. No... it's not ANSI/ISO compliant and no... it won't work with DATETIME2() data types. Of course, if you know me, you'll know that I don't believe in either when it comes to T-SQL, especially when it comes to performance. 😛
DECLARE @UPH datetime = '2014-07-29 08:16:31.000';
SELECT CAST(@UPH AS DECIMAL(17,12))%1*24;
The DATETIME is converted to decimal.
That is play against Modulus(1) (%1) to return only the fractional part of the decimal value, which is TIME represented as a fractional day.
That is multiplied by 24 hours because there are 24 hours in 1 day, and we're done.
It's a real shame that you can't convert the DATETIME2() directly to decimal to make such problems so easy.
Short, sweet (and ansi sour):-P
😎