Get the time of a datetime using T-SQL?

sql-server tsql datetime sql-server-2000
grady picture grady · Sep 7, 2010 · Viewed 149.8k times · Source

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?

Answer

AdaTheDev picture AdaTheDev · Sep 7, 2010

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.

Related questions

How to return only the Date from a SQL Server DateTime datatype

SELECT GETDATE() Returns: 2008-09-22 15:24:13.790 I want that date part without the time part: 2008-09-22 00:00:00.000 How can I get that?

Read More
SQL query to select dates between two dates

I have a start_date and end_date. I want to get the list of dates in between these two dates. Can anyone help me pointing the mistake in my query. select Date,TotalAllowance from Calculation where EmployeeId=1 and Date …

Read More
Sql Server string to date conversion

I want to convert a string like this: '10/15/2008 10:06:32 PM' into the equivalent DATETIME value in Sql Server. In Oracle, I would say this: TO_DATE('10/15/2008 10:06:32 PM','MM/DD/YYYY HH:MI:SS AM') This question implies that I …

Read More