CODEWITHSUNDEEP
sql-serverdatetime
Nate
Nate

Reputation: 2047

Convert oracle date string to SQL Server datetime

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?

Upvotes: 3

Views: 15041

Answers (2)

THN
THN

Reputation: 41

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

Upvotes: 0

anon
anon

Reputation:

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.

Upvotes: 2

Related Questions