Error converting data type DBTYPE_DBDATE to datetime.

Question

Error converting data type DBTYPE_DBDATE to datetime.

Ray Waldron

Old Hand

Points: 353
More actions
June 29, 2005 at 10:46 am

#89863

Hi all ..Please help..This has been driving me nuts..
I have a linked server is SQL to a Pervasive DB...
If I run the query
SELECT *
From OPENQUERY(servicebase_live_link, 'SELECT * from contacts')
I get the correct databack as the table contacts just contains Datatypes CHAR.
However when I run the query
SELECT *
From OPENQUERY(servicebase_live_link, 'SELECT * from jobs')
I get the error Error converting data type DBTYPE_DBDATE to datetime.
I know this is because the Table jobs contains datatypes of CHAR,DATE,INTEGER,TIME and to start with I know the date datatype must be casing problems....
I have tried returning just one column (date_booked) and casting certain fields (date_booked) to get around this but none work.
Such as
SELECT *
From OPENQUERY(servicebase_live_link, 'select CAST(date_booked AS DATETIME) from jobs')
Can someone please help me get this to work ...
Do I need to explicitly set all the fields that are set to these datatypes ?
Thx in advance
Ray..

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

I do not know Pervasive DB, but can you show us an example of the date_booked data? Maybe conversion to varchar, then datetime will solve your problem.

CONVERT( datetime, CONVERT( varchar(25), date_book)) ?

I wasn't born stupid - I had to study.

noeld SSC Guru Points: 96590 More actions · Answer 2

I don't know Pervasive DB either but I am pretty sure that the cast Construct is a MS proprietary thing. You should find out what are the functions used there for data convertion. When you use Openquery the query is sent to the Provider EXACTLY as you type it

If to connect to it you are using an ODBC driver I would guess that you could try to use the ODBC scalar convertion function Like

SELECT *

From OPENQUERY(servicebase_live_link, 'select {fn convert (date_booked , SQL_DATE)} from jobs')

hth

* Noel

Bangaaram SSC-Addicted Points: 465 More actions · Answer 3

noeld (6/29/2005)
I don't know Pervasive DB either but I am pretty sure that the cast Construct is a MS proprietary thing. You should find out what are the functions used there for data convertion. When you use Openquery the query is sent to the Provider EXACTLY as you type it
If to connect to it you are using an ODBC driver I would guess that you could try to use the ODBC scalar convertion function Like
SELECT *
From OPENQUERY(servicebase_live_link, 'select <STRONG>{fn convert (date_booked , SQL_DATE)}</STRONG> from jobs')
hth

This didn't help me out. Can you suggest some other plz.

Thanks

-----------------------------------------------------------------------
Known is a DROP, Unknown is an OCEAN.:ermm:

Sergiy SSC Guru Points: 110208 More actions · Answer 4

Ray Waldron (6/29/2005)
I know this is because the Table jobs contains datatypes of CHAR,DATE,INTEGER,TIME and to start with I know the date datatype must be casing problems....

If those are only data types in that table, then where the message about DBTYPE_DBDATE comes from?

_____________
Code for TallyGenerator