IsDate() Else set to null - SQL Server Forums

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

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

IsDate() Else set to null

Author

Topic

rwaldron
Posting Yak Master

131 Posts

Posted - 2005-06-30 : 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 - 2005-06-30 : 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 - 2005-06-30 : 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 - 2005-06-30 : 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

131 Posts

Posted - 2005-06-30 : 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

5 Posts

Posted - 2008-04-29 : 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 - 2008-04-29 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources