CODEWITHSUNDEEP
sql-servert-sqldatetime
Geoffrey West
Geoffrey West

Reputation: 281

How to convert SQL server 24 hour time string to 12 hour datetime data type

I have a column named origdttime that is a text field. I would like to convert this to a 12 hour time field. The values of the field arehh:mi:ss.mmm i.e. 06:46:00.000 I would like for this time to appear as 06:46 AM

I am using the statement CONVERT(varchar(15), origdttime ,8) AS [time] however it is returning the exact same format and values.

What needs to be adjusted to return my desired output?

Upvotes: 1

Views: 6868

Answers (2)

Matt
Matt

Reputation: 14381

While performance is not always great using FORMAT() if you are on SQL-Server 2012 + it is convenient option especially for smaller data sets etc.

DECLARE @Table AS TABLE (TimeAsString VARCHAR(50))
INSERT INTO @Table VALUES ('06:46:00.000'),('17:46:00.000')

SELECT
   TimeAsString
   ,FORMAT(CAST(TimeAsString as DATETIME), 'hh:mm tt')
FROM
    @Table

Upvotes: 2

M.Ali
M.Ali

Reputation: 69574

Try ....

SELECT CONVERT(VARCHAR(15),CAST('06:46:00.000' AS TIME),100)

Result: 

6:46AM

SELECT CONVERT(VARCHAR(15),CAST('17:46:00.000' AS TIME),100)

Result: 

5:46PM

Upvotes: 7

Related Questions