Get the time of a datetime in TSQL?

Question

Hello, how could I do this? I have a datetime in DB like this: 2010-09-06 17:07:28.170

What I want is only 17:07:28.170. Is there a function for that or something?

Thanks :-)

Answer 1

In case of SQL Server, this should work

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

Answer 2

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

SELECT CONVERT(char,GETDATE(),14) 

Edited to include millisecond.

Answer 3

I think what you meant to say was "What I want is only 17:07:28.170". The DATEPART() function will help you here.

Answer 4

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 5

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

IN SQL Server 2008+

CAST(GETUTCDATE() AS TIME)