How to convert varchar to datetime

[Sailor]

I have a SQL table with a varchar field that contains dates in two formats:
04:16:59 Oct 12, 2005 PDT
and
10/17/2007 8:01:19 AM
(the field may also be empty)

(it's inserted there by an automated PayPal IPN script, so I can't change how it's entered). I just want to be able to display these in a consistent format. But when I do reports, it's impossible to sort this field by date.
I've tried convert(char(25),payment_date,110) and cast(payment_date as datetime) and various combinations of those, but keep getting errors
"Syntax error converting datetime from character string."

Anyone have any hints how I can do this? Again, I don't want to convert the field type, just how it's displayed when I do queries.

Thanks

 

[Eksith]

This is very tricky since an error will either choke your whole application or worse, end up with null fields where you don't want any...

Best to try the Date add method.

But before you do, always check for null fields via IsDBNull

//=== C#
if(Field != System.DBNull.Value) {
 // Convert
}

'=== VB
If Field <> System.DBNull.Value Then
 ' Convert
End if