Error converting data type DBTYPE_DBDATE to datetime

[rwaldron]

Hi all,
The guys in the SQL tek tips reckon I should try this pervasive forum instead to help me....
I think I am finally stuck on the pervasive syntax..


I have a linked server in SQL to Pervasive DB.
When I run a distributed select * statement to the linked server, it fails when the results reach the first datetime record that contains a 0 (Zero).

The pervasive DB design contains some data types of 'date' and they contain a valid date but alot of them also contain zero.I cannot change the design of this DB.

I know its the zero in the date field causing problems because When I run a select top 3493 on the DB I get back the date row ok...
As soon as I go one higher 3494. I get the error
"Error converting data type DBTYPE_DBDATE to datetime"

Is there some syntax to check that the data is a real date and if not set to Null.
I know there is an 'isdate' function but I am not sure of the syntax..

Here is an example

SELECT top 3493 *
From OPENQUERY(sbase_local, 'select date_booked from jobs')

retuns 3493 columns of 2002-02-21 00:00:00.000

if I use
SELECT top 3494 *
From OPENQUERY(sbase_local, 'select date_booked from jobs')

I get the "Error converting data type DBTYPE_DBDATE to datetime"

The record 3494 is the first record the query hits where date_booked contains a zero and not a date.
-------------------------------------------------
The guys in sql reckon ----

Basically you need to do something to replace the 0s with actual date values before the data gets to SQL Server, ie within the OPENQUERY query.

But I myself would prefer to replace the 0's with null

In SQL Server you would use a CASE expression:


CODE
FROM OPENQUERY(sbase_local, 'SELECT CASE WHEN date_booked = 0 THEN ''19000101'' ELSE date_booked END AS date_booked FROM jobs')