×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Error converting data type DBTYPE_DBDATE to datetime

Error converting data type DBTYPE_DBDATE to datetime

Error converting data type DBTYPE_DBDATE to datetime

(OP)
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

RE: Error converting data type DBTYPE_DBDATE to datetime

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

RE: Error converting data type DBTYPE_DBDATE to datetime

(OP)
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 ??

RE: Error converting data type DBTYPE_DBDATE to datetime

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

RE: Error converting data type DBTYPE_DBDATE to datetime

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

-SQLBill

Posting advice: FAQ481-4875

RE: Error converting data type DBTYPE_DBDATE to datetime

(OP)
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..

RE: Error converting data type DBTYPE_DBDATE to datetime

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.

RE: Error converting data type DBTYPE_DBDATE to datetime

(OP)
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?

RE: Error converting data type DBTYPE_DBDATE to datetime

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

RE: Error converting data type DBTYPE_DBDATE to datetime

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

RE: Error converting data type DBTYPE_DBDATE to datetime

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

RE: Error converting data type DBTYPE_DBDATE to datetime

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.   

RE: Error converting data type DBTYPE_DBDATE to datetime

(OP)
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..

RE: Error converting data type DBTYPE_DBDATE to datetime

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:

CODE

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

--James

RE: Error converting data type DBTYPE_DBDATE to datetime

(OP)
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::Prepare 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..

RE: Error converting data type DBTYPE_DBDATE to datetime

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:

CODE

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

--James

RE: Error converting data type DBTYPE_DBDATE to datetime

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

-SQLBill

Posting advice: FAQ481-4875

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close