CODEWITHSUNDEEP
sql-serversql-server-2008
user2883955
user2883955

Reputation: 25

Date conversion

I have a table with a datetime column I want to retrieve the date in hh:mm:ss where hours is 12hour.

I have tried the following.

 convert(varchar(10), t2.CSM_START_TIME,108),
 convert(varchar(2),datepart(hour,t2.CSM_START_TIME))
    +':'+convert(varchar(2),datepart(mm,t2.CSM_START_TIME))
    +':'+convert(varchar(2), datepart(SECOND,t2.CSM_START_TIME))  
    as START_TIME

Upvotes: 0

Views: 54

Answers (3)

user2883955
user2883955

Reputation: 25

This got me what I wanted.

,substring(convert(varchar(40), t2.CSM_START_TIME,109),12,9)+' '+substring(convert(varchar(40), t2.CSM_START_TIME,109),25,2)

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280615

SELECT LTRIM(RIGHT(CONVERT(CHAR(20),GETDATE(),22),11));

Result:

11:40:15 PM

Or if you really don't want the AM/PM (which I don't understand):

SELECT LTRIM(LEFT(RIGHT(CONVERT(CHAR(20), GETDATE(),22),11),8));

Result:

11:40:15

Much, much, much better to format this at the client. For example, if you are using C#, look at .Format() and .ToString(). Don't make SQL Server do your dirty presentation work, especially when you have much more flexible features in a more powerful language.

Upvotes: 3

Manwal
Manwal

Reputation: 23836

you should try this 

DATE_FORMAT(NOW(),'%h:%i %p')

or you will find more here http://www.w3schools.com/sql/func_date_format.asp.

Upvotes: -1

Related Questions