Solved

Trouble Pulling from a FoxPro ODBC Connection to SQL

Posted on 2014-01-16
6
542 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VMware PVSCSI SQL Server 2016 AlwaysOn 2 37
When are cursors useful? 8 61
Enable TLS 1.2 for SQL 2012 Web Edition 1 24
Datatable / Dates ? 4 30
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

734 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