Error converting data type DBTYPE_DBTIMESTAMP to datetime.

[skystar]

Hi,
I would like to migrate an oledb database to sql 2005 database
While converting i got "Error converting data type DBTYPE_DBTIMESTAMP to datetime."
I have severl colones that i need to convert them to datetime at the same time in the same querry.

To begin i hve tried this but it's not working :

select date_clotu FROM OPENROWSET(
   'MSDASQL',
   'Driver={Microsoft dBase Driver (*.dbf)};DBQ=C:\Documents and Settings\oledb',
   'Select TO_CHAR(date_clotu) FormattedDate  from SQDI')where isdate(FormattedDate)=0 ;

Can u help me plz ...

[rmiao]

What's the value in DBTIMESTAMP type column look like? Tried load them in char type column first then convert?

[skhanal]

dbase date must be out of range for sql server or date is not valid.

[skystar]

The value have this format "07/01/2005 00:00:00".

i still need ur help , 10x

[MAK]

First go to the oledb database and update all the invalid dates. then run the import.

check BOL for SQL Server 2000/2005 datetime range values

I have seen invlaid dates in other RDBMS like 01/01/0001. Possible only in SQL Server 2008.