SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

rwaldron
Posting Yak Master

129 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
SELECT *
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 NULL.name each column in the select statement instead of using "select *".

Can anyone help me with the correct code please :(

Cheers.

Ray.

KLang23
Posting Yak Master

115 Posts

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

Use Northwind
Go
SELECT     
  CustomerCode, 
  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

KLang23
Posting Yak Master

115 Posts

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

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

KLang23
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.

Thanks.
Go to Top of Page

rwaldron
Posting Yak Master

129 Posts

Posted - 06/30/2005 :  12:13:58  Show Profile  Reply with Quote
Hiya I have tried
SELECT *
From OPENQUERY(servicebase_live_link, 'SELECT
date_booked,
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...

Ray


Go to Top of Page

jjradha
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.



IF OBJECT_ID('tempdb..#JOB_TAB') IS NOT NULL
DROP TABLE #JOB_TAB

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


regards,



Marcelo Miorelli
Go to Top of Page

joseln
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
or
set dateformat mdy

rgds
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.06 seconds. Powered By: Snitz Forums 2000