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?
 
Chris MConnect With a Mentor Consulting - 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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.