Error converting data type DBTYPE_DBDATE to datetime

[rwaldron]

Hi all,
I posted something similar a couple of weeks ago but still no joy...

I have a linked server in SQL to Pervase 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 are to be allowed contain a valid date or a 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.

Any Help appretiated

Ray

 

[jbenson001]

Select < your column list>,
<date col> =
case ISDATE(<date col>)
when 0 then '0'
when 1 then startdate
end
from <table>

 

[rwaldron]

Hi,
Thx for the reply..

Ive tried filling in the syntax..
My linked server is called sbase_local , table is date_booked

so
Select date_booked =
case ISDATE(date_booked)
when 0 then '0'
when 1 then startdate
end
from sbase_local.cellularlocal..jobs

I get the response Invalid column name 'startdate'.
if i change startdate to date_booked
ie:

Select date_booked =
case ISDATE(date_booked)
when 0 then '0'
when 1 then date_booked
end
from sbase_local.cellularlocal..jobs

Then I'm back to the error Error converting data type DBTYPE_DBDATE to datetime.

If I run Select top 10 date_booked =
case ISDATE(date_booked)
when 0 then '0'
when 1 then date_booked
end
from sbase_local.cellularlocal..jobs

I get the first 10 rows back ok....
I'm doing something stupid ??

 

[jbenson001]

Try changing the name of the column to something else
Select date_booked =
case ISDATE(date_booked)
when 0 then '0'
when 1 then date_booked
end
from sbase_local.cellularlocal..jobs

Select DateBooked =
case ISDATE(date_booked)
when 0 then '0'
when 1 then date_booked
end
from sbase_local.cellularlocal..jobs

 

[SQLBill]

You also posted this question in THREAD183-1084694. You really should have followed up in that thread.

-SQLBill

Posting advice: FAQ481-4875

 

[rwaldron]

Hiya,
Thx for the reply,

I tried

Select DateBooked =
case ISDATE(date_booked)
when 0 then '0'
when 1 then date_booked
end
from sbase_local.cellularlocal..jobs
but I still get the error
Error converting data type DBTYPE_DBDATE to datetime

If I use select 'top' up to where I know they are all good dates then the data comes back ok. Anything above this and the error occurs so your syntax is ok but just isn't having an effect as far as converting ..

should I create a similar table in SQL and try and update this table rather than running a select statement on a linked statement..

 

[jbenson001]

This is definatly a data issue. YOu have to check and see what the data looks like. Are you sure all the dates are valid except where there are 0s? I don't think so, somewhere you have bad data.

 

[rwaldron]

Hiya,
I agree, its bad data but I think that the bad data is the zero in the date fieled. if I try add another column 'ref_number' that exists in pervasive as integer into my query then I get the error.


Syntax error converting the varchar value 'ref_number' to a column of data type int.

I know exactly where the data halts and its the first time that the query hits a zero in the date field..
I think SQL would only expect either a valid Date or NULL..

If I create an empty Table in SQL and somehow update this from the sql table...would this work..
i'm not sure of how to use the update syntax...
This would have to be constantly updated for it to be live?

 

[jbenson001]

You are doing a select, so the IsDate funtion will take care of the problem. You are not inserting or updateing so there is no reason to convert anyting, you are simply selecting data

 

[jbenson001]

what is the exact column definition where the date is being stored?

 

[rwaldron]

Hiya ,
The exact column definition where the date is being stored in pervasive is of type DATE

 

[micha123]

Lets look again at your original (?) query:

Select date_booked =
case ISDATE(date_booked)
when 0 then '0'
when 1 then date_booked
end
from sbase_local.cellularlocal..jobs

The problem is that '0' and date_booked are not of the same datatype.
You must change either of them to be the same as the other.
For example:

Select date_booked =
case ISDATE(date_booked)
when 0 then '0'
when 1 then convert(varchar(40),date_booked)
end
from sbase_local.cellularlocal..jobs

Did this solve your problem...?




"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.

 

[rwaldron]

hiya, Thx for the reply...

I tried the code you said but I still get the error.
converting data type DBTYPE_DBDATE to datetime as soon as sql comes accross the first date that has 0

Select date_booked =
case ISDATE(date_booked)
when 0 then '0'
when 1 then convert(varchar(40),date_booked)
end
from sbase_local.cellularlocal..jobs

any more help appretiated,
Ray..

 

[JamesLean]

I think the problem is you need to do the date checking at the sybase end, rather than use ISDATE on the SQL Server end. Have you tried this:

SELECT * FROM OPENQUERY(sbase_local, 'select date_booked from jobs where date_booked <> 0')

--James

 

[rwaldron]

Hiya,
I tried you code but got the error
OLE DB provider 'MSDASQL' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandPrepare:repare returned 0x80004005: The provider did not give any information about the error.]


I don't think pervasive likes the string
'select date_booked from jobs where date_booked <> 0')

Then I tried

SELECT *
From OPENQUERY(sbase_local, 'select date_booked from jobs') where date_booked <> 0

Code ran ok but then I got the previous error
converting data type DBTYPE_DBDATE to datetime

just another note though...I don't want to skip records that contain zero...I also need to pull these across too?

Thx,

Ray..

 

[JamesLean]

I've never used Pervasive before so I don't know the exact syntax it uses. 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.

In SQL Server you would use a CASE expression:

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

--James

 

[SQLBill]

Have you considered posting this in one of the TEK-TIPS Pervasive Forums?

-SQLBill

Posting advice: FAQ481-4875