Here am Giving a Function am using in my applications
Try this..
CREATE FUNCTION [dbo].[FormatDateTime](@strDATE varchar(30))
RETURNS datetime
WITH EXECUTE AS CALLER
AS
BEGIN
RETURN (CAST(
SUBSTRING(@strDATE,3,2) + '-' +
SUBSTRING(@strDATE,1,2) + '-' +
SUBSTRING(@strDATE,5,4) + ' ' +
SUBSTRING(@strDATE,9,2) + ':' +
SUBSTRING(@strDATE,11,2) + ':' +
SUBSTRING(@strDATE,13,2) as datetime));
END;
@strDate = ddmmyyyyHHmiss
Thanks