Dbase to SQL

Last Post 08 Nov 2004 02:53 PM by Krypto. 7 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
Krypto
New Member
New Member

--
08 Nov 2004 06:26 AM
We have a Dbase 5 file we want to import into SQL2000. Everything goes in fine, except the date fields. Data overflow, invalid character cast specification.

How can we get the dates in? We would like to use the native DBF file, instead of converting to text.
Krypto
New Member
New Member

--
08 Nov 2004 07:25 AM
Krypto
New Member
New Member

--
08 Nov 2004 07:44 AM
They look like .... 05 10 2004

Thanks in advance...
Krypto
New Member
New Member

--
08 Nov 2004 09:45 AM
Yeah, I could do that and then run a convert, then change to datetime. I was hoping for a 'cleaner' way...Wouldn't even mind changing it in the DBF.


Thanks RM.
Krypto
New Member
New Member

--
08 Nov 2004 11:30 AM
If I can do that....then SQL will okay with it, correct?
Krypto
New Member
New Member

--
08 Nov 2004 11:34 AM
Thanks again RM.
Krypto
New Member
New Member

--
08 Nov 2004 02:53 PM

Just for conversations sake..


I figured I would have to use convert...even had something written. I was just thinking that maybe there was a more native way to get the date field without any direct manipulation.

Reason I am throwing this out is that we are converting a very old system that has millions of records that are somewhat date inconsistant . Many records came from different propriortary systems. We have exported thru Access, Fox Pro, Text dump, etc....most records turn out fine but some don't. The ones that don't sometimes switch positions. (ie. 10/12/2004 becomes 12/10/2004) This is within the date field(s)itself. All other fields are fine.. I know it's tough cause these are very old and inconsistant records.

Is there a DBF to SQL date/time convert utility prog...script..?

Thanks again RM...always helpful.
Billpl
New Member
New Member

--
11 Nov 2004 11:37 AM
I know I'm late in this discussion but... When it comes to these types of problems, the old saying comes to mind..."Dance with whomever you came to the dance with". The best way to manage dbase files is to break out a copy of dbase or FoxPro, open up a ODBC connection, write a few routines and fire away. DBase and FoxPro both have built it logic to handle the conversions. so you don't have to.

As far as dates are concerned. If you different date formats in different files and they are true Date fields, DBase should be able to translate them for you and pass them off to SQL. (the format type is designated in the header of the DBF file). If you have month and day switched around in the same file, your DBF is corrupt and nothing will help you.


Acceptable Use Policy
---