Error converting Oracle Date timestamp to Sql datatime

Hello Experts,

I am in the middle of designing a SP where in i have to import some data elements from oracle DB to SQL server db. In this process i am importing it into a temp table. i am getting the below error during import on tran_Date column.

"Error converting data type DBTYPE_DBTIMESTAMP to datetime.

Now i have narrowed down the no of columns from 20 to 5 and tested with the below solution posted on some website.

Insert into SQL_TABLE( col1, col2, col3, tran_date, col5)
select * from openquery (LINKED_SERVER, select col1, col2, col3, fn convert(tran_date,SQL_TIMESTAMP)}

Now this works perfectly and now when i go back and include this part of my actual select statement which has to pull 20 columns, i am getting the above error which is


"Error converting data type DBTYPE_DBTIMESTAMP to datetime".

Any suggestions?
parpaaAsked:
Who is Participating?
 
grzegorzsCommented:
I use function in openquery (query send to Oracle):

to_char(oracle_column, 'yyyy-mm-dd HH:MI:SS')

Next in SQL Server database there is no problem with conversion to datetime.

best regards
Grzegorz
0
 
matty1stopCommented:
Is your oracle date field nullable?  A null value will throw this error upon conversion
0
 
parpaaAuthor Commented:
I changed to 'NOT NULL', but still the error exists..
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
parpaaAuthor Commented:
If the nullable was the problem, then it would have not worked when i narrowed the table to 5 columns also right?

0
 
matty1stopCommented:
try changing this
  fn convert(tran_date,SQL_TIMESTAMP)
to this
  CAST([tran_date] AS varchar(26))

0
 
parpaaAuthor Commented:
i got this error

An error occurred while preparing the query "SELECT .... From Oracle_Table

for execution against OLE DB provider "MSDASQL" for linked server
0
 
parpaaAuthor Commented:
sorry the error desc wasn't complete

[Microsoft][ODBC driver for Oracle][Oracle]ORA-00936: missing expression".

An error occurred while preparing the query "SELECT .... From Oracle_Table

for execution against OLE DB provider "MSDASQL" for linked server
0
 
matty1stopCommented:
can you post the entire statement?
0
 
parpaaAuthor Commented:
CREATE TABLE #SQL_TEMP(
      COL1 NVARCHAR(40) NULL,
      TRAN_DATE datetime NULL,
      COL3 NVARCHAR(40) NULL,
      COL4 NVARCHAR(50) NULL,
      COL5 varchar(75) NULL)
      


INSERT INTO #SQL_TEMP
SELECT * from OPENQUERY(LINKED_SERVER,'SELECT COL1,CAST(TRAN_DATE AS varchar(26)), COL3, COL4, COL5
 FROM SAMREPOSITORY')

The above query works

CREATE TABLE #SQL_TEMP(
      col1 nvarchar(100) NULL,
      TRAN_DATE DATETIME NULL,
      COL3nvarchar(100) NULL,
      COL4 nvarchar(75) NULL,
      COL5 NVARCHAR(15) NULL,
      COL6 nvarchar(256) NULL,
      COl7 nvarchar(40) NULL,
      COL8 nvarchar(256) NULL,
      COL9 nvarchar(100) NULL,
      COL10 nvarchar(50) NULL,
      COl11 nvarchar(40) NULL,
      COL12 varchar(60) NULL,
      COL13 varchar(20) NULL,
      COL14 varchar(20) NULL,
      COL15 nvarchar(100) NULL,
      COL16 NVARCHAR(40) NULL,
      COl17 datetime NULL,
      COl18 NVARCHAR(40) NULL,
      COl19 NVARCHAR(50) NULL,
      COl20 varchar(75) NULL)



INSERT INTO #SQL_TEMP
select * from OPENQUERY(LINKED_SERVER,'SELECT  COL1,CAST(Tran_date AS varchar(26)),COL2,
COL3,COL4,COL5,COL6,COL7,COl8,COL9,COl10,COl11,COl12,COl13,COl14,COL15,COL16,COL17,Col18,COl19,COl20FROM ORACLE_TABLE')'

THe above query fails with error
Error converting data type DBTYPE_DBTIMESTAMP to datetime.
0
 
matty1stopCommented:
Can you cut and paste the entire statement you are trying to execute?
0
 
parpaaAuthor Commented:
This is exactly what i am trying to execute, i just renamed the column names and table names, everything else is same.
0
 
matty1stopCommented:
Right, sorry, browser brought up the cached page without your newest comment
0
 
matty1stopCommented:
Does this return anything?


SELECT * FROM OPENQUERY(LINKED_SERVER, 'SELECT * FROM ORACLE_TABLE WHERE TRAN_DATE is NULL OR TRAN_DATE < ''1/1/1753''');
0
 
parpaaAuthor Commented:
got the error below

The OLE DB provider "MSDASQL" for linked server "LINKED_SERVER" reported an error. The provider did not give any information about the error.
0
 
matty1stopCommented:
OK,  how many records are in the table?  Will this run in a reasonable time frame?

select * from OPENQUERY(LINKED_SERVER,'SELECT  Tran_date  FROM ORACLE_TABLE order by tran_date')
0
 
parpaaAuthor Commented:
THere are 88k rows and the result is of the format '25-OCT-07'

I casted the tran_Date and executed your above suggestion


select * from OPENQUERY(LINKED_SERVER,'SELECT CAST(TRAN_DATE AS varchar(26)) FROM ORACLE_TABLE order by TRAN_DATE')
0
 
parpaaAuthor Commented:
If i execute the Tran_Date as part of whole query , it fails again. Is there a possibility that temp table is unable to handle that much data?
0
 
matty1stopCommented:
I don't think too much data is the issue

How about this?

select *
from openquery(LINKED_SERVER, 'select TO_CHAR((TRAN_DATE ) FormattedDate from ORACLE_TABLE ) derived
where isdate(FormattedDate)=0
0
 
parpaaAuthor Commented:
I got the same error above again...

Well i got around this problem by creating 2 temp tables populating it seperately and it worked fine now.
0
 
parpaaAuthor Commented:
I took every column other than Tran_Date into #temp1 and tran_date into #temp2 and then joining the tables.
0
 
matty1stopCommented:
Strange.  Sorry I wasn't much help
0
 
parpaaAuthor Commented:
No problem. Though it is solved i still wanted to know the exact cause for this strange behaviour.
0
 
awking00Commented:
You're showing col17 as datetime. Shouldn't you also have to use the cast function on the source data?
0
 
parpaaAuthor Commented:
@awking00 - It should be varchar, sorry for the typo..

@grzegorzs - I still got the error converting varchar to date error from your suggestion, but i changed 'tran_date' datatype to 'varchar' from datetime and it worked now.

Thanks for your help Everyone!!
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.