SQL Server 2008 Convert String to Datetime question

Question

I have a string column in a table that display data as "CXL P/D 08/15/13"

I'm trying to convert this column to datetime, but I can't figure out how to extract only date and change the data type.

Cast(RIGHT(RTRIM(Trade_Date) ,8)as datetime) I'm trying this statement but it doesn't work

Conversion failed when converting date and/or time from character string.

Thank you

Answer 1

Use CONVERT and specify 1 as the date time style 1 = mm/dd/yy.

CONVERT(datetime, RIGHT('CXL P/D 08/15/13', 8), 1)