Get the time of a datetime using T-SQL?

Sql ServerTsqlDatetimeSql Server-2000

Sql Server Problem Overview

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?

Sql Server Solutions

Solution 1 - Sql Server

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 - Sql Server

In case of SQL Server, this should work

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

Solution 3 - Sql Server

Try this

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

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

Solution 4 - Sql Server

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

SELECT CONVERT(char,GETDATE(),14)

Edited to include millisecond.

Solution 5 - Sql Server

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

IN SQL Server 2008+

CAST(GETUTCDATE() AS TIME)

Solution 6 - Sql Server

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

 SELECT CONVERT(VARCHAR(5),getdate(),108)
Categories
Recommended Sql Server Solutions
Recommended Tsql Solutions
Recommended Datetime Solutions
Recommended Sql Server-2000 Solutions

Previous Solution:

How to efficiently use Rprof in R?

RProfilingProfiler

Next Solution:

How do you return to a sourced bash script?

BashSubprocessReturnExit

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestiongradyView Question on Stackoverflow
Solution 1 - Sql ServerAdaTheDevView Answer on Stackoverflow
Solution 2 - Sql ServerJagmagView Answer on Stackoverflow
Solution 3 - Sql ServermauroxView Answer on Stackoverflow
Solution 4 - Sql ServerMichael ShimminsView Answer on Stackoverflow
Solution 5 - Sql ServerSimmoView Answer on Stackoverflow
Solution 6 - Sql ServerAli ahmadkhaniView Answer on Stackoverflow