CODEWITHSUNDEEP
sql-servert-sqldatetimesql-server-2000
grady
grady

Reputation: 12785

Get the time of a datetime using T-SQL

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?

Upvotes: 78

Views: 181947

Answers (7)

a_b_c_d_e_f_g_h_i_j __
a_b_c_d_e_f_g_h_i_j __

Reputation: 11

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

Upvotes: 1

maurox
maurox

Reputation: 1302

Try this:

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

See time (Transact-SQL).

Upvotes: 14

Simmo
Simmo

Reputation: 3131

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

In SQL Server 2008 and later

CAST(GETUTCDATE() AS TIME)

Upvotes: 6

Michael
Michael

Reputation: 20069

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

SELECT CONVERT(char,GETDATE(),14)

Upvotes: 10

Ali ahmadkhani
Ali ahmadkhani

Reputation: 170

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

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

Upvotes: 0

AdaTheDev
AdaTheDev

Reputation: 147354

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.

Upvotes: 117

Jagmag
Jagmag

Reputation: 10366

In case of SQL Server, this should work

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

Upvotes: 21

Related Questions