CONVERT to HH:MM AM/PM

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Patrick Rouse (anonymous_at_discussions.microsoft.com)
Date: 04/27/04 

Date: Tue, 27 Apr 2004 13:06:07 -0700

I have a table storing the time as varchar(25), but want to display it on a report as HH:MM w/ AM or PM, i.e. 12:05AM or 12:05PM (no date, just the time)

I've tried a bunch of CONVERT statements listed in the BOL and in previous threads, but the best I've gotten is with 108 which gives me HH:MM:SS w/o the AM/PM.

Is there a way to do this with TSQL?

Thanks.