Selecting Oracle date into SQL Server 2005

I am trying to Select data from our Oracle tables into SQL Server 2005.  I'm getting the following error:  Error converting data type DBTYPE_DBTIMESTAMP to datetime.
I tried doing a convert on the Oracle date
Select statement:
SELECT     CAST(DATEPART(YYYY, CONVERT(datetime, dm.DATE_SOLD)) AS Int) AS YEAR_SOLD, CAST(DATEPART(MM, CONVERT(datetime,
                      dm.DATE_SOLD)) AS Int) AS MONTH_SOLD, CONVERT(datetime, dm.DATE_SOLD) AS Date_Sold, dm.DIE_NUMBER,
                      CASE dm.DIE_SYSTEM_NUMBER WHEN 1 THEN 'NEW' ELSE 'SERVICE DIE' END AS ORDER_TYPE, dm.DIE_TYPE_ID, dm.DIE_SYSTEM_NUMBER,
                      dmo.DIE_MODE_CODE, ds.DIE_STATE_DESCRIPTION
FROM         DTS..DIEMFG.DIE_MFG_DETAIL AS dm INNER JOIN
                      DTS..DIEMFG.DIE_STATES AS ds ON dm.DIE_STATE_ID = ds.DIE_STATE_ID INNER JOIN
                      DTS..DIEMFG.DIE_MODES AS dmo ON dm.CURRENT_DIE_MODE_ID = dmo.DIE_MODE_ID
WHERE     (dm.DATE_SOLD > '1/1/2008') AND (dm.DATE_SOLD IS NOT NULL) AND (dm.DIE_TYPE_ID <> 978) OR
                      (dm.DIE_TYPE_ID <> 978) AND (ds.DIE_STATE_DESCRIPTION = 'SOLD')
ORDER BY YEAR_SOLD, MONTH_SOLD, dm.DATE_SOLD

Based on the error message I was thinking the date from Oracle needed to be converted to SQL Server 2005 datetime data type.  Do I need to format the date instead or something else altogether?  Any help or suggestions would be greatly appreaciated!
bkrbbnsEEAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris MConsulting - Technology ServicesCommented:
I used to have this issue but I worked it up with OPENQUERY function.

Just use SQL plus to write an oracle compatible query and try is out.
Ensure that using Oracle date functions, you return a date which is compartible with SQL server's.

So then after that, you can use this:

SELECT * FROM OPENQUERY(Linked_Server_Name, ' Select ..... from Oracle_tables');


E.g:
SELECT * FROM OPENQUERY(DTS, 'Select .........')

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chris MConsulting - Technology ServicesCommented:
Try this (Remember to escape the single quotes as i have done by adding another single quote)

Test the syntax in Oracle.

SELECT * FROM OPENQUERY(DTS,
'SELECT  dm.DATE_SOLD As YEAR_SOLD, dm.DATE_SOLD MONTH_SOLD, 
		dm.DATE_SOLD AS Date_Sold, dm.DIE_NUMBER,
                      CASE dm.DIE_SYSTEM_NUMBER WHEN 1 THEN ''NEW'' ELSE ''SERVICE DIE'' END AS ORDER_TYPE,
		dm.DIE_TYPE_ID, dm.DIE_SYSTEM_NUMBER,
                      dmo.DIE_MODE_CODE, ds.DIE_STATE_DESCRIPTION
FROM DIEMFG.DIE_MFG_DETAIL AS dm INNER JOIN
	DIEMFG.DIE_STATES AS ds ON dm.DIE_STATE_ID = ds.DIE_STATE_ID INNER JOIN
    DIEMFG.DIE_MODES AS dmo ON dm.CURRENT_DIE_MODE_ID = dmo.DIE_MODE_ID
WHERE     (dm.DATE_SOLD > ''1/1/2008'') AND (dm.DATE_SOLD IS NOT NULL) AND (dm.DIE_TYPE_ID <> 978) OR
                      (dm.DIE_TYPE_ID <> 978) AND (ds.DIE_STATE_DESCRIPTION = ''SOLD'')
ORDER BY YEAR_SOLD, MONTH_SOLD, dm.DATE_SOLD');

Open in new window

0
bkrbbnsEEAuthor Commented:
Thank you very much for your help and quick response.  Very much appreciated.
0
bkrbbnsEEAuthor Commented:
What I actually ran:  (THANKS AGAIN!)
SELECT     YEAR_SOLD, MONTH_SOLD, [TO_CHAR(DMD.DATE_SOLD,'MM/DD/YYYY')], DIE_NUMBER, ORDER_TYPE, DIE_TYPE_ID, DIE_SYSTEM_NUMBER,
                      DIE_MODE_CODE, DIE_STATE_DESCRIPTION
FROM         OPENQUERY(DTS,
                      'SELECT   CAST (TO_CHAR (dmd.date_sold, ''YYYY'') AS INT) AS year_sold,
            CAST (TO_CHAR (dmd.date_sold, ''MM'') AS INT) AS month_sold,
            TO_CHAR (dmd.date_sold, ''MM/DD/YYYY''), dmd.die_number,
            CASE dmd.die_system_number
               WHEN 1
                  THEN ''NEW''
               ELSE ''SERVICE DIE''
            END AS order_type,
            dmd.die_type_id, dmd.die_system_number, dm.die_mode_code,
            ds.die_state_description
       FROM diemfg.die_mfg_detail dmd INNER JOIN diemfg.die_states ds
            ON dmd.die_state_id = ds.die_state_id
            INNER JOIN diemfg.die_modes dm
            ON dmd.current_die_mode_id = dm.die_mode_id
      WHERE (    dmd.date_sold > TO_DATE (''1/1/2009'', ''MM/DD/YYYY'')
             AND dmd.date_sold IS NOT NULL
            )
        AND (   (dmd.die_type_id <> 978)
             OR (dmd.die_type_id <> 978 AND ds.die_state_description = ''SOLD''
                )
            )
   ORDER BY CAST (TO_CHAR (dmd.date_sold, ''YYYY'') AS INT),
            CAST (TO_CHAR (dmd.date_sold, ''MM'') AS INT),
            dmd.date_sold')
                       AS derivedtbl_1
0
Chris MConsulting - Technology ServicesCommented:
Great stuff :-)
I'm happy that you're happy & like the working solution :-)

God bless.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.