| Author |
 Topic  |
|
|
rwaldron
Posting Yak Master
129 Posts |
 Posted - 06/30/2005 : 10:49:35
|
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
|
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. |
 |
|
|
KLang23
Posting Yak Master
115 Posts |
Posted - 06/30/2005 : 11:04:33
|
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. |
|
|
|
KLang23
Posting Yak Master
115 Posts |
Posted - 06/30/2005 : 11:35:11
|
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. |
|
|
|
rwaldron
Posting Yak Master
129 Posts |
Posted - 06/30/2005 : 12:13:58
|
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
|
|
|
|
jjradha
Starting Member
United Kingdom
5 Posts |
Posted - 04/29/2008 : 11:30:56
|
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 |
|
|
|
joseln
Starting Member
11 Posts |
Posted - 04/29/2008 : 11:43:44
|
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
|
|
|
| |
Topic |
|
IsDate() Else set to null
