Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rwaldron
Posting Yak Master
131 Posts |
 Posted - 2005-06-29 : 12:23:52
|
| 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 asSELECT *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 advanceRay.. |
|
|
KLang23
Posting Yak Master
115 Posts |
Posted - 2005-06-29 : 13:55:05
|
| Hi,Sometimes in legacy systems there are dates that are invalid to SQL Server. You can use a "Case" statement in your "select" with a check for "ISDate()" adding some other clever tests to only return dates that are valid else NULL. You will have to name each column in the select statement instead of using "select *".Cheers. |
 |
|
|
rwaldron
Posting Yak Master
131 Posts |
Posted - 2005-06-30 : 10:18:25
|
| Hiya,You where correct about my Problem..Some of the data in my date_booked column is zero and not a Date..If I run the qeury up to a certain amount of rows it returns ok but as soon as it hits the first date = 0 it stops..You Said "You can use a "Case" statement in your "select" with a check for "ISDate()" adding some other clever tests to only return dates that are valid else NULL. You will have to name each column in the select statement instead of using "select *".My Current statement is SELECT *From OPENQUERY(servicebase_live_link, 'select date_booked,from jobs')Can you help me with the case,isDate else set to Null Please ??Ray |
|
|
|
|
|
|
Error converting data type DBTYPE_DBDATE to dateti