Get time in AM/PM Time format in SQL Server

There are times that you want to display non-Military or AM/PM time format. The standard Convert formats do not have an option to return the time in this format. The SQL below will return only the time portion of the current date/time in the AM/PM format. 

 

SELECT substring(convert(varchar(20), GetDate(), 9), 13, 5)
+ ' ' + substring(convert(varchar(30), GetDate(), 9), 25, 2)

You can use this to update see your custom results by updating getdate() with your date object.