Get the time of a datetime using T-SQL?

Question

How to get the time for a given datetime value?

I have a datetime in database like this:

2010-09-06 17:07:28.170 

and want only the time portion:

17:07:28.170 

Is there a function for that or something?

Answer 1

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.

Answer 2

In case of SQL Server, this should work

SELECT CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinuteSecond