How to get time part from SQL Server 2005 datetime

Question

How to get time part from SQL Server 2005 datetime in HH:mm tt format

E.g.

11:25 AM
14:36 PM

Answer 1

You'll need two converts, one to get the HH:mm time, and one to get AM/PM. For example:

declare @date datetime
set @date = '20:01'
SELECT CONVERT(VARCHAR(5), @date, 108) + ' ' +
       SUBSTRING(CONVERT(VARCHAR(19), @date, 100),18,2)

This prints:

20:01 PM

In a select query, replace @date with your column's name.

Answer 2

You need to use CONVERT function:

CONVERT(VARCHAR, yourdatetimefiled, 114) AS [HH:MI(12H)]

Answer 3

One way is:

SELECT LTRIM(RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100), 7))

If you have a look at Books Online here, format 100 is the one that has the time element in the format you want it in, it's just a case of stripping off the date from the front.

Answer 4

select substring(CONVERT(VARCHAR, getdate(), 114),1,5)

resault : 22:05

Answer 5

select right(convert(char(20),getdate(),0),7)

No check though

Answer 6

This gives you an actual datetime and not varchar

CAST(LEFT(YOURDATETIME,12) AS SMALLDATETIME) AS YOURNEWDATE