Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 609
  • Last Modified:

Trouble Pulling from a FoxPro ODBC Connection to SQL

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
lm1189
Asked:
lm1189
1 Solution
 
ravikantninaveCommented:
try
SELECT TO_CHAR(datafield)
0
 
ravikantninaveCommented:
or
CAST([SomeDateField] AS varchar(26))
0
 
lm1189Author Commented:
I tried cast, cast didn't work.  Do I put this in the OPENQUERY SELECT or the normal select?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
pcelbaCommented:
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
 
jrbbldrCommented:
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
 
Olaf DoschkeSoftware DeveloperCommented:
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now