Your query returns
1:00PM
, which is very close to your desired
1PM
. There doesn't appear to be a built-in date format for that, but you can use
REPLACE(..., ':00', '')
to achieve it:
SELECT REPLACE(CONVERT(varchar(15), Cast(Time AS TIME), 100), ':00', '') from table_Name