Solved

Trouble Pulling from a FoxPro ODBC Connection to SQL

Posted on 2014-01-16
6
529 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

 
LVL 42

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

756 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