Error converting data type

Hello,
I got error when i trying to import data from foxpro table to sql:
SELECT * INTO #Temp1 FROM openrowset('MSDASQL', 'Driver=Microsoft Visual
FoxPro Driver;SourceType=DBF;SourceDB=c:', 'select record_dt from
[MY.DBF]')
Server: Msg 8114, Level 16, State 10, Line 1
Error converting data type DBTYPE_DBDATE to datetime.
how to fix? Thanks.I don't know Foxpro; is there some way you can convert the record_dt column
into, perhaps, a string in the OPENROWSET, then convert the column to a
DATETIME back on the SQL Server side? It's just a matter of incompatible
formats...
Something like:
SELECT * INTO #Temp1 FROM openrowset('MSDASQL', 'Driver=Microsoft Visual
FoxPro Driver;SourceType=DBF;SourceDB=c:', 'select
CONVERT_TO_STRING(record_dt) from
[MY.DBF]')
Replace CONVERT_TO_STRING with however you convert to a string in Foxpro.
By the way, the safest format to use for dates in SQL Server is the ISO
format: YYYYMMDD HH:MM:SS.MS
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"js" <js@.someone@.hotmail.com> wrote in message
news:O2WDB6L5FHA.3908@.tk2msftngp13.phx.gbl...
> Hello,
> I got error when i trying to import data from foxpro table to sql:
> SELECT * INTO #Temp1 FROM openrowset('MSDASQL', 'Driver=Microsoft Visual
> FoxPro Driver;SourceType=DBF;SourceDB=c:', 'select record_dt from
> [MY.DBF]')
> Server: Msg 8114, Level 16, State 10, Line 1
> Error converting data type DBTYPE_DBDATE to datetime.
> how to fix? Thanks.
>
>|||Apparently some of the records are not in datetime format -
try to add where isdate(trade_dt)= 1
SELECT * INTO #Temp1 FROM openrowset('MSDASQL', 'Driver=Microsoft
Visual
FoxPro Driver;SourceType=DBF;SourceDB=c:', 'select record_dt from
[MY.DBF]') where isdate(trade_dt)= 1
to see the offending records run the following :
SELECT * INTO #Temp1 FROM openrowset('MSDASQL', 'Driver=Microsoft
Visual
FoxPro Driver;SourceType=DBF;SourceDB=c:', 'select record_dt from
[MY.DBF]') where isdate(trade_dt)= 0|||Thanks for the help.
Got this error:
Could not execute query against OLE DB provider 'MSDASQL'.
OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandText::Execute returned
0x80040e37].
I found some values are '1899-12-30 00:00:00.000', how to fix this in a
query FoxPro can run?
<hussein.safa@.gmail.com> wrote in message
news:1131498021.441399.109230@.g44g2000cwa.googlegroups.com...
> Apparently some of the records are not in datetime format -
> try to add where isdate(trade_dt)= 1
> SELECT * INTO #Temp1 FROM openrowset('MSDASQL', 'Driver=Microsoft
> Visual
> FoxPro Driver;SourceType=DBF;SourceDB=c:', 'select record_dt from
> [MY.DBF]') where isdate(trade_dt)= 1
> to see the offending records run the following :
>
> SELECT * INTO #Temp1 FROM openrowset('MSDASQL', 'Driver=Microsoft
> Visual
> FoxPro Driver;SourceType=DBF;SourceDB=c:', 'select record_dt from
> [MY.DBF]') where isdate(trade_dt)= 0
>