74

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?

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
grady
  • 12,281
  • 28
  • 71
  • 110

7 Answers7

113

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.

AdaTheDev
  • 142,592
  • 28
  • 206
  • 200
  • 5
    This is a good answer. @grady you should accept this or ask for more information. – Mark S Mar 13 '15 at 13:53
  • 1
    Or more precisely: convert(TIME[(fractional second precision)], GETDATE()) (use a precision of 0 to truncate subseconds) – tbone Jun 23 '16 at 21:39
  • 1
    @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. – Reversed Engineer Nov 27 '17 at 16:11
  • 4
    @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 – AdaTheDev Nov 27 '17 at 16:47
  • @AdaTheDev Ok, I tried to reverse my downvote, but it said it was too many hours ago now. – Reversed Engineer Nov 28 '17 at 07:32
21

In case of SQL Server, this should work

SELECT CONVERT(VARCHAR(8),GETDATE(),108) AS HourMinuteSecond
Jagmag
  • 10,283
  • 1
  • 34
  • 58
14

Try this:

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

See time (Transact-SQL).

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
maurox
  • 1,254
  • 1
  • 19
  • 33
10

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

SELECT CONVERT(char,GETDATE(),14)
Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Michael Shimmins
  • 19,961
  • 7
  • 57
  • 90
5
CAST(CONVERT(CHAR(8),GETUTCDATE(),114) AS DATETIME)

In SQL Server 2008 and later

CAST(GETUTCDATE() AS TIME)
Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Simmo
  • 3,101
  • 3
  • 16
  • 15
0

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

 SELECT CONVERT(VARCHAR(5),getdate(),108)
Ali ahmadkhani
  • 170
  • 11
0

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

a_b_c_d_e_f_g_h_i_j __
  • 1
  • 2
  • 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? – Jeremy Caney Aug 23 '23 at 06:19