Ho to convert MSSQL Date to HH:MM AM/PM Format

SELECT Substring(CONVERT(VARCHAR(30), GETDATE(), 9),13,5)+' '+
Substring(CONVERT(VARCHAR(30), GETDATE(), 9),25,2)

OutPut: 1:22 PM


GETDATE() :Returns the Curent Date.
Substring(Str,initial,length)

For further reference look at :http://www.sqljunkies.ddj.com/Article/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk