Get the time of a datetime using T-SQL?
Just to add that from SQL Server 2008, there is a TIME datatype so from then on you can do:
SELECT CONVERT(TIME, GETDATE())
Might be useful for those that use SQL 2008+ and find this question.
In case of SQL Server, this should work
SELECT CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinuteSecond