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?

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

Solution 2

In case of SQL Server, this should work

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

Solution 3

Try this

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

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

Solution 4

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

SELECT CONVERT(char,GETDATE(),14) 

Edited to include millisecond.

Solution 5

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

IN SQL Server 2008+

CAST(GETUTCDATE() AS TIME)

View more solutions

Comments

[maurox]

in msdn documentation there isn't a sql version. i use it in sql server 2008

[Mark S]

This is a good answer. @grady you should accept this or ask for more information.

[element11]

Perfect for SQL Server 2005

[tbone]

Or more precisely: convert(TIME[(fractional second precision)], GETDATE()) (use a precision of 0 to truncate subseconds)

[Reversed Engineer]

@MarkS this cannot be the accepted answer, since it does not work on SQL 2000 (which the OP explicitly specifies). There is no TIME type in SQL 2000. In fact it shouldn't have any votes at all, since it doesn't answer the question.

[AdaTheDev]

@DaveBoltman - agree with your point re: not being the accepted answer. However, I do find the vote reasoning + downvote harsh. Although not for the SQL 2000 as per the exact question, it's still relevant (and correct) for later versions. As questions get old, it becomes more and more relevant, as the votes indicate. If this had a lot of downvotes into the negative, it would incorrectly indicate that it's just wrong. People searching now will more than likely (hopefully!) be on > 2000

[Reversed Engineer]

@AdaTheDev Ok, I tried to reverse my downvote, but it said it was too many hours ago now.

[FindOutIslamNow]

This is useful for editing a date while keeping the time