Get the time of a datetime using T-SQL

Question

How can I 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?

Freezing

What are those magic numbers in answers: 108, 14, and 114? They are a bit terse. – Brose

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.

Somewise

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

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

@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. – Sanguineous

@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 – Somewise

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

Answer 2

In case of SQL Server, this should work

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

Exhaust

Answer 3

Try this:

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

See time (Transact-SQL).

Ilarrold

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

Answer 4

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

SELECT CONVERT(char,GETDATE(),14) 

Plasmodium

Answer 5

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

In SQL Server 2008 and later

CAST(GETUTCDATE() AS TIME)

Inveracity

Answer 6

i know i m late. but,

A1 Convert(nvarchar, Convert(smalldatetime, getDate())) as [small date time],

Aug 21 2023 9:36AM

A2 Convert(time(2), Convert(smalldatetime, getDate())) as [small time],

09:36:00.00

ps, Convert(time(x), GetDate()) where x >= 0 and x =< 7

EDIT, right, so, following up with the comment below i can understand how my answer might look similar to some of the answers above.

The first code A1 converts the given date to nvarchar which is already been provided, above with different type (smalldatetime) which should return a shorter value than both datetime, and datetime2

Where the second code A2 converts the given date to time with (2) as the milliseconds instead of the default (6), which can range between 0 and 7 milliseconds displayed

Mekong

Thank you for your interest in contributing to the Stack Overflow community. This question already has quite a few answers—including one that has been extensively validated by the community. Are you certain your approach hasn’t been given previously? If so, it would be useful to explain how your approach is different, under what circumstances your approach might be preferred, and/or why you think the previous answers aren’t sufficient. Can you kindly edit your answer to offer an explanation? – Bakki

Answer 7

You can try the following code to get time as HH:MM format:

 SELECT CONVERT(VARCHAR(5),getdate(),108)

Bridgeport