How to show Time only with AM/PM only in a SQL Query?

Question

I have the following problem, I am using SQL Server MS and below is my result. It looks simple but I cant figure it out.

My query:

SELECT  RIGHT(CONVERT(VARCHAR(26), Timein, 109), 14) from vwSignIn

Will give me this as a 9:12:16:597AM

I want this 9:12:16 AM as my result.

Thanks in Advance.

Answer 1

Here's an approach with only one conversion:

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

However you should consider formatting this on the client side, where string formatting is much more powerful and appropriate.

In SQL Server 2012, you will be able to use FORMAT(), which means you don't have to memorize all of these style numbers (but I still think you're better off doing this in the presentation layer when possible):

SELECT FORMAT(GETDATE(), 'hh:mm:ss tt');

Just beware, FORMAT() is expensive relative to other approaches:

• FORMAT() is nice and all, but…
• FORMAT is a convenient but expensive function – Part 1
• FORMAT is a convenient but expensive function – Part 2

Answer 2

Here's one way you can do it:

CONVERT(VARCHAR(8), Timein, 108) + ' ' + RIGHT(CONVERT(VARCHAR(30), Timein, 9), 2) 

SQL Fiddle Demo