AM PM time converstion in SQL Server from string

Please read my comment first.

Generally, to convert data from one type to another, please use: CAST and CONVERT (Transact-SQL)[^]

[EDIT]
Reply to OP comment. No, it's not enough information. There are few version of MS SQL Server. Some of them does not provide time data type.

DECLARE @t VARCHAR(30) = '1133'

SELECT LEFT(@t,2) + ':' + RIGHT(@t,2) + ' AM' AS MyTimeAsString

SELECT CONVERT(TIME, MyTimeAsString) AS MyTimeAsTime
FROM (
    SELECT LEFT(@t,2) + ':' + RIGHT(@t,2) + ' AM' AS MyTimeAsString
    ) AS T

SELECT CONVERT(DATETIME, MyTimeAsString) AS MyTimeAsDateTime
FROM (
    SELECT LEFT(@t,2) + ':' + RIGHT(@t,2) + ' AM' AS MyTimeAsString
    ) AS T

Results:

MyTimeAsString
11:33 AM

MyTimeAsTime
11:33:00.0000000

MyTimeAsDateTime
1900-01-01 11:33:00.000

Is that what you want?
[/EDIT]

[EDIT 2]

CREATE FUNCTION udf_FormatTime (@sTime VARCHAR(10))
    RETURNS VARCHAR(15)
AS
BEGIN
    DECLARE @sTimeFormat VARCHAR(15)

    IF LEN(@sTime) = 0
        SET @sTimeFormat = '00:00 AM'
    ELSE
        SET @sTimeFormat = LEFT(@sTime, 2) + ':' + RIGHT(@sTime, 2) 
    
    IF CONVERT(INT, LEFT(@sTimeFormat,2))<13 
        SET @sTimeFormat = @sTimeFormat + ' AM'
    ELSE
       SET @sTimeFormat = @sTimeFormat + ' PM'
    
RETURN @sTimeFormat
END

[/EDIT 2]