I have a query that runs against a linked server (tables are in dBase). When
I run a simple select statement against one of the tables, I get an error:

"Error converting data type DBTYPE_DBTIMESTAMP to datetime"

Is there a way of tracing which record has this bad data in it?

I'm running SQL SERVER 2008 R2

Thanks!

Jerry Haynes (JerryHaynes (AT) discussions (DOT) microsoft.com) writes:
Does DBBASE provide something similar to convert, so that you can receive
the data as text? The you can use the isdate() function to find the bad
dates in it.

You would run this query throuh OPENQUERY. I don't think it would work to
run something like:

SELECT convert(varchar, col) FROM DBASE...tbl

because the conversion error would happen before convert.

My guess is that it is not a case of a bad date per se, that is rubbish like
2010-02-29, but either a date of range, that is before 1753, or a value with
a higher precision than milliseconds.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Wed, 28 Jul 2010 23:41:37 +0200, Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:

Just one B.

What dialect he is using matters. I use Microsoft Visual FoxPro
myself. It has a function DTOS() which converts a date to string in
the form <YYYYMMDD>. It also has TTOC() which converts a datetime to
string in the form <YYYYMMDDhhmmss>. TTOC()'s second parm should be 1
for this.

[snip]

Sincerely,

Gene Wirchenko