IsDate() Else set to null

[rwaldron]

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.

 

[vongrunt]

Can you solve this problem in openquery (PervasiveSQL syntax)? Say, to let query return NULL instead of zero...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[rwaldron]

Hiya ,
So the openquery ()refers to the pervasive synatx..
I am not to Familar with pervasive..

I did try the following from somebody else but i don't think pervasive understands the case command..

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
')

It was also suggested to me to
create 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..

It was suggested that I 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.

but I'm not sure what a cleansing query is ??

Any Ideas?

Ray..