Solved

Trouble Pulling from a FoxPro ODBC Connection to SQL

Posted on 2014-01-16
6
500 Views
Last Modified: 2014-03-29
I'm trying to pull data from a Foxpro database into SQL using a ODBC connection with the foxpro driver.  It is getting the error:

'ERROR CONVERTING DATA TYPE DBTYPE_DBTIMESTAMP to datetime'

My query is:

SELECT 
*
FROM         OPENQUERY([master_builder], 'select duedte from acpinv') AS ACP_INV

Open in new window


How can I in my select query either cast the data so it comes through or not pull bad dates?

Here's a screenshot if it helps:  http://i.imgur.com/V3wKEK2.png
0
Comment
Question by:lm1189
6 Comments
 
LVL 4

Expert Comment

by:ravikantninave
ID: 39786171
try
SELECT TO_CHAR(datafield)
0
 
LVL 4

Expert Comment

by:ravikantninave
ID: 39786177
or
CAST([SomeDateField] AS varchar(26))
0
 

Author Comment

by:lm1189
ID: 39786296
I tried cast, cast didn't work.  Do I put this in the OPENQUERY SELECT or the normal select?
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 41

Expert Comment

by:pcelba
ID: 39786470
It is always better to push (upload) data from FoxPro to SQL Server but that's not this case...

And yes, DateTime is causing problems sometimes...

Try to convert the DateTime to string at FoxPro side as the first attempt:

SELECT * FROM OPENQUERY([master_builder], 'select TTOC(duedte) from acpinv') AS ACP_INV

Then you may investigate what causes problems. It could be empty or null datetime field in FoxPro table.

Could you test just a small FoxPro table containing one DateTime field which is not empty?

And one question: You are using VFP OLE DB provider to pull data from FoxPro probably. ODBC driver is not so easy to use. Am I correct?
0
 
LVL 12

Accepted Solution

by:
jrbbldr earned 500 total points
ID: 39788553
The over-riding question is - Is this a one-time operation or something that needs to be done on an on-going basis?

If a one-time operation - There are a number of ways to Export Foxpro data tables into a different format such as CSV, etc. which SQL Server can more readily Import.

If this is needed to be done repeatedly - Then there are certainly field value issues to have to deal with - most noteably FP Date fields --> SQL Server DateTime fields.

With a better understanding of your needs, we can better assist you.

Good Luck
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 39792112
Look out for empty dates in the DBF,there is no value for that in SQL Server. Most probably that's leading to the conversion error.

Bye, Olaf.

EDIT:

If you're not knowing Foxpro: empty differs from NULL in vfp. There is an empty date, with the characteristics to be displayed as "  /  /    " or "  .  .    " depending on your locale and being before any other date in comparisons like somedate<another date, which makes it a good value as ultimo date. You can't compare NULL with some other date. So in some cases the empty date is best replaced with the earliest date SQL Server can handle, in other cases with NULL.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Microsoft Visual FoxPro (short VFP) is a programming language with it’s own IDE and database, ranking somewhat between Access and VB.NET + SQL Server (Express). Product Description: http://msdn.microsoft.com/en-us/vfoxpro/default.aspx (http://msd…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now