Try this (it will not handle microseconds):
create table #yourtable(Order_Date_Time varchar(30),realdatetime datetime);
insert into #yourtable
values ('6/29/2015 6:28 AM',null)
,('6/29/2015 6:58',null)
,('6/29/2015 24:15:00',null)
,('6/29/2015 25:30:00',null)
;
update a
set realdatetime=dateadd(ss
,b.s
,dateadd(mi
,b.m
,dateadd(hh
,b.h
,b.d
)
)
)
from #yourtable as a
inner join (select distinct
Order_Date_Time
,convert(datetime,substring(Order_Date_Time,1,charindex(' ',Order_Date_Time)-1),101) as d
,convert(int,substring(Order_Date_Time,charindex(' ',Order_Date_Time)+1,charindex(':',Order_Date_Time)-(charindex(' ',Order_Date_Time)+1)))
+case
when lower(right(rtrim(substring(Order_Date_Time,charindex(':',Order_Date_Time)+4,len(Order_Date_Time))),2))='pm'
then 12
else 0
end as h
,convert(int,substring(Order_Date_Time,charindex(':',Order_Date_Time)+1,2)) as m
,case lower(substring(Order_Date_Time,charindex(':',Order_Date_Time)+4,2))
when 'am' then 0
when 'pm' then 0
when ' ' then 0
else convert(int,substring(Order_Date_Time,charindex(':',Order_Date_Time)+4,2))
end as s
from #yourtable
) as b
on b.Order_Date_Time=a.Order_Date_Time
;
select * from #yourtable;
drop table #yourtable;