I'd stage it into a CHAR column and mung it into one of the accepted date input formats (see http://www.karaszi.com/SQLServer/info_datetime.asp#DtFormatsInput) using SUBSTRING() and a CASE expression.
Something along the lines of:
CREATE TABLE dbo.date_test
(
ip CHAR(11),
op DATE
);
INSERT INTO dbo.date_test (ip) VALUES ('1994_Sep_28'), ('2011_Nov_30'), ('2012_Dec_21');
UPDATE dbo.date_test
SET op = SUBSTRING(ip, 1, 4)
+ '-'
+ CASE SUBSTRING(ip, 6, 3)
WHEN 'Jan' THEN '01'
WHEN 'Feb' THEN '02'
WHEN 'Mar' THEN '03'
WHEN 'Apr' THEN '04'
WHEN 'May' THEN '05'
WHEN 'Jun' THEN '06'
WHEN 'Jul' THEN '07'
WHEN 'Aug' THEN '08'
WHEN 'Sep' THEN '09'
WHEN 'Oct' THEN '10'
WHEN 'Nov' THEN '11'
WHEN 'Dec' THEN '12'
END
+ '-'
+ SUBSTRING(ip, 10, 2);
SELECT * FROM dbo.date_test;