Converting datetime format to 12 hour

Question

I have this query

select CONVERT(varchar(5), tdate ,108) AS [Time] from table 

which gives me the time in 24 hour format( military)

I wanted to convert it into a 12 hour format so i tried the query below

 select SUBSTRING(CONVERT(VARCHAR, tdate, 100),13,2) + ':' 
+ SUBSTRING(CONVERT(VARCHAR, tdate, 100),16,2) + ''
+ SUBSTRING(CONVERT(VARCHAR, tdate, 100),18,2) AS T  
from table

and i get the 12 hour format but I am just curious if there is a shorter or better way of doing it. any help?

Answer 1

If you want to convert the current datetime for example:

SELECT CONVERT(VARCHAR, getdate(), 100) AS DateTime_In_12h_Format

Instead of getdate() you can put your desired column in a query (such as tdate in your example). If you want JUST the time in 12h and not the date and time use substring/right to separate them. It seems that you already know how to =).

This page lists every datetime conversion. It's really handy if you need other types of conversions.

Answer 2

This will return just the time, not the date.

SELECT RIGHT(CONVERT(VARCHAR, getdate(), 100), 7) AS time

For your table data:

select RIGHT(CONVERT(varchar, tdate ,100), 7) AS [Time] from table 

Answer 3

Below code will return only time like 10:30 PM

SELECT FORMAT(CAST(getdate() AS DATETIME),'hh:mm tt') AS [Time]