Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 734
Nithesh Narayanan Avatar asked Jan 20 '11 09:01

Nithesh Narayanan


People also ask

How do I show time in 12 hour format in SQL?

We can use DATEPART() function to get the HOUR part of the DateTime in Sql Server, here we need to specify datepart parameter of the DATEPART function as hour or hh.

How do I convert 24 hour format to 12 hour format?

For example if the time in 12 hour clock is 11:00 PM, then the time in 24 hour clock is 11:00 + 12:00 = 23:00 hours.

How do you format the 12 hour datetime?

Change Time format to HH:mm:ss for a 24-hour clock. Change Time format to hh:mm:ss tt for a 12-hour clock.


2 Answers

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.

like image 194
Damien_The_Unbeliever Avatar answered Oct 21 '22 02:10

Damien_The_Unbeliever


Sometimes you need to do it inline:

SELECT CONVERT(varchar(15),  CAST(GETDATE() AS TIME), 100) as AmPmTime
like image 44
rAdmin Avatar answered Oct 21 '22 01:10

rAdmin