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]