Convert oracle date string to SQL Server datetime

Question

In a SQL Server 2000 DB, I have a table which holds string representations of Oracle DB dates. They are formatted like "16-MAY-12". I need to convert these to datetime. I can not seem to find a conversion style number that matches, nor can I find a function that will allow me to specify the input format. Any ideas?

Solution

This seems to work for me:

SELECT CONVERT(DATETIME, '16-MAY-12');

You can also try using TO_CHAR() to convert the Oracle values to a more SQL Server-friendly format (best is YYYYMMDD) before pulling them out of the darker side.