Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 IsDate() Else set to null
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Posting Yak Master

131 Posts

Posted - 06/30/2005 :  10:49:35  Show Profile  Reply with Quote
Hi all,Thx for the help so far

I was receiving the "error "converting data type DBTYPE_DBDATE to datetime" on my select statement to a Linked pervasive DB.
I now know that this is due to the fact that some fields on the linked server conatain Zero's instead of a valid date , even though the data type is set to date...

My select statement runs perfectly until it hits the first occurance of a date as zero and then it stops..

My current Statement is
From OPENQUERY(servicebase_live_link, 'Select date_booked from jobs')

After some help from you guys I now Know I need to do the following..

Use a "case" stament in my "select" with a check for "ISDate()" adding some other tests to only return dates that are valid else each column in the select statement instead of using "select *".

Can anyone help me with the correct code please :(



Posting Yak Master

115 Posts

Posted - 06/30/2005 :  10:55:41  Show Profile  Reply with Quote
Go your email.

Use Northwind
  CASE WHEN isDate(DueDate) = 1 THEN DueDate ELSE NULL END as DueDate
FROM Customer

You will have to remove the "Select *" and replace it will a column list.
Go to Top of Page

Posting Yak Master

115 Posts

Posted - 06/30/2005 :  11:04:33  Show Profile  Reply with Quote

Post only once, and in only one section.
If you flood the forum you'll not only get flamed, but you probably won't receive any more assistance.
Go to Top of Page

Posting Yak Master

115 Posts

Posted - 06/30/2005 :  11:35:11  Show Profile  Reply with Quote
Place the new "select" statement inside the openquery, replacing the olde "select *" statement.

The target database may not understand the syntax of the "CASE" statement. If this is the case, I suggest that you create an interrum table that looks similar to the original target, but had the date columns defined as varchar.

Then run the "cleansing" query against the interrum table to populate the original target.

Please use the forums and not email.

Go to Top of Page

Posting Yak Master

131 Posts

Posted - 06/30/2005 :  12:13:58  Show Profile  Reply with Quote
Hiya I have tried
From OPENQUERY(servicebase_live_link, 'SELECT
CASE WHEN isDate(date_booked) = 1 THEN date-booked ELSE NULL END as date_booked
FROM jobs

But I get the error
An error occurred while preparing a query for execution against OLE DB provider 'MSDASQL'.
OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandPrepare::Prepare returned 0x80040e14].

So I think you are correct and that pervasive doesn't understand the case statement..

So I will try go with what you said about creating an interum table..
I was trying to avoid this though as the table has 200 columns and 60,000 records...

I do not want to effect or insert to the pervasive database in anyway..

What is this cleansing query?
I will create a blank DB in sql that looks similar to the original pervasive data base..
How do I go about using a cleansing query to populate this new blank datbase? This would need to be a daily update as the datbase is the backend of a website and not a data migration.

Thx again...


Go to Top of Page

Starting Member

United Kingdom
5 Posts

Posted - 04/29/2008 :  11:30:56  Show Profile  Reply with Quote
Hi all,
Regarding the message "Error converting data type DBTYPE_DBDATE to datetime"
I finally got a solution.
In my case I am using SQL Server 2000.
I have a linked server called LIVE_ANG which has the Progress 9.1 database.

The progress and SQL were not understanding each other regarding the datetime.

then started to use the progress way to convert data to string, and all is fine now.
Follow is the example.


create table #JOB_TAB (

rownum int identity (1,1) primary key not null,
jobdate datetime,
item nchar(30)

); /*table #JOB_TAB definition*/

SET @sql_str =
'SELECT string(job-date),item
FROM job
WHERE job = ''' + @JOB + ''''

SET @sql_str = N'insert into #JOB_TAB (jobdate,item) select * from OPENQUERY(LIVE_ANG, ''' + REPLACE(@sql_str, '''', '''''') + ''')'

EXEC (@sql_str)

if ( exists( select rownum from #JOB_TAB )) begin

/*-----------------------------------------------------------* The job was found and now we look for the items
select top 1 @item = item,
@jobdate = jobdate
from #JOB_TAB


Marcelo Miorelli
Go to Top of Page

Starting Member

11 Posts

Posted - 04/29/2008 :  11:43:44  Show Profile  Reply with Quote
Hi All

ISDATE might not handle data properly. but as alternate, based on which format you want, you can write as

set dateformat dmy
set dateformat mdy

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000