Get datetime time using T-SQL?

Question

How to get time for a given datetime value?

I have a datetime in the database:

 2010-09-06 17:07:28.170 

and want only the temporary part:

 17:07:28.170 

Is there a function for this or something else?

Answer 1

To add this from SQL Server 2008, there is a TIME data type, so from now on you can:

 SELECT CONVERT(TIME, GETDATE()) 

May be useful for those using SQL 2008+ and find this question.

Answer 2

In the case of SQL Server, this should work.

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

Answer 3

Assuming the title of your question is correct and you want time:

 SELECT CONVERT(char,GETDATE(),14) 

Edited to include milliseconds.

Answer 4

try it

 SELECT CAST(DataField AS time(7)) AS 'time' 

http://msdn.microsoft.com/en-us/library/bb677243.aspx

Answer 5

 CAST(CONVERT(CHAR(8),GETUTCDATE(),114) AS DATETIME) 

In SQL Server 2008 +

 CAST(GETUTCDATE() AS TIME)