CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Nithesh Narayanan
Nithesh Narayanan

Reputation: 11775

Converting a time into 12 hour format in SQL

How can i convert 24 hour formatted time into 12 hour formatted time in SQL server 2008?

Upvotes: 11

Views: 111067

Answers (6)

FARHAN KHAN
FARHAN KHAN

Reputation: 59

SQL can perform inline conversion for time

mytime in database is 4:15:00

SELECT CONVERT(varchar(15),  CAST(mytime AS TIME), 100) as my_time from mytable

Upvotes: 1

Santhosh VS
Santhosh VS

Reputation: 51

Select Right(IsNull(Convert(Varchar,GetDate(),100),''),7)

Upvotes: 5

Jonny Lawrence
Jonny Lawrence

Reputation: 119

To add to rAdmin's comment, the syntax is:

CONVERT(data_type,expression,style_code)

Here are some other style codes you can use:

SELECT
GETDATE() AS GetDate,
CONVERT(VARCHAR,GETDATE(),0) AS '0',
CONVERT(VARCHAR,GETDATE(),100) AS '100',
CONVERT(VARCHAR,GETDATE(),1) AS '1',
CONVERT(VARCHAR,GETDATE(),101) AS '101',
CONVERT(VARCHAR,GETDATE(),7) AS '7',
CONVERT(VARCHAR,GETDATE(),107) AS '107',
CONVERT(VARCHAR,GETDATE(),108) AS '108',
CONVERT(VARCHAR,GETDATE(),108) AS '108',
CONVERT(VARCHAR,GETDATE(),10) AS '10',
CONVERT(VARCHAR,GETDATE(),110) AS '110',
CONVERT(VARCHAR,GETDATE(),12) AS '12',
CONVERT(VARCHAR,GETDATE(),112) AS '112',
CONVERT(VARCHAR,GETDATE(),14) AS '14',
CONVERT(VARCHAR,GETDATE(),114) AS '114'

Tried to post an image of the result set, but I need to "Earn more than 10 reputation to post images".

Upvotes: 7

rAdmin
rAdmin

Reputation: 71

Sometimes you need to do it inline:

SELECT CONVERT(varchar(15),  CAST(GETDATE() AS TIME), 100) as AmPmTime

Upvotes: 7

nvogel
nvogel

Reputation: 25534

SQL Server doesn't format the time. It's your client application that controls how the time is displayed. You should change the time format settings in your application or your code.

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

Do you have the current time in a variable/column of type time? If so, it's easy:

declare @t time
set @t = '14:40'

select CONVERT(varchar(15),@t,100)

result:

2:40PM

If it's in a datetime(2) variable, then you'll have to strip out the date portion after running the CONVERT. If it's in a string, then first CONVERT it to time, then use the above code.

Upvotes: 30

Related Questions