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?

[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.

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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
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

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
awking00Information Technology SpecialistCommented:
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
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

From novice to tech pro — start learning today.