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?
-
What is the problem? Are you getting an error message? If so, what is it? What are your regional / language settings? Also, what does September 18th look like?Aaron Bertrand– Aaron Bertrand2012-05-16 18:51:12 +00:00Commented May 16, 2012 at 18:51
Add a comment
|
2 Answers
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.
3 Comments
Nate
The convert did not work for me. Syntax error 241. I gave up on the SQL Server end and took your suggestion to extract the date from Oracle in a friendlier format. Thanks for your time!
Aaron Bertrand
You probably have other dates that don't look quite like above. Or, the SQL Server side has regional / language settings that make the format less acceptable (this is why I asked about these earlier).
Follow Aaron's advice and cast to string on the Oracle side and then did a check/recast on the MS SQL side. See example below:
;WITH SOURCE AS (
SELECT * FROM openquery(lnk,
'SELECT
TO_CHAR(OFFDATE , ''YYYY-MM-DD HH24:MI:SS'') AS OFFDATE,
FROM
ORACLE_SOURCE')),
SOURCE_TRANSFORM AS
(
SELECT
CASE
WHEN ISDATE(OFFDATE) = 1 THEN CAST(OFFDATE AS DATETIME)
ELSE NULL END AS OFFDATE
FROM
SOURCE
)
SELECT * FROM SOURCE_TRANSFORM
