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;

1. The DATETIME is converted to decimal.

2. That is played against Modulus(1) (%1) to return only the fractional part (the REMAINDER from the MODULUS) of the decimal value, which is TIME represented as a fractional day.

3. 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.