convert a field in DBF files [Archive]

View Full Version : convert a field in DBF files

dmly

06-16-03, 12:03

Hi,

I have a DBF file with a text field containing date information. How can I convert the text field to date format automatically. I know I can use Access to convert it but it's time consuming. Anybody know any utility, please help???

RafM

04-06-04, 20:02

Hi! This is a frequent problem with old database environments and appeared initially in the "x-base" family when people started to migrate dBaseII files (no "date" type) to dBase III.

Assuming the text string is 10 characters long and in the "dd/mm/yyyy" format all you would have to do (having previously SET DATE BRITISH) is REPLACE ALL datefield WITH CTOD(textfield)

If the text string has no slashes and no dashes, then it's more cumbersome ... you have to build the argument for the CTOD (characters to date) function piece by piece. Let's suppose the text string contains dates like "ddmmyyyy". Then you would REPLACE ALL datefield with CTOD(SUBSTR(textfield,1,2)+"/"+SUBSTR(textfield,3,2)+"/"+SUBSTR(textfield,5,4)).

If the format were "mmddyyyy" you would have to transpose the SUBSTRings a little bit.

Good luck!

QUOTE]Originally posted by dmly
Hi,

I have a DBF file with a text field containing date information. How can I convert the text field to date format automatically. I know I can use Access to convert it but it's time consuming. Anybody know any utility, please help??? [/QUOTE]

DSummZZZ

04-07-04, 13:36

dmly,

Are you trying to convert the data within the field to look like date or are you trying to convert the actual data type of the field from character type to date type?
What version of Fox are you using?

If you are using VFP (I believe ver. 6 or greater), you can use this statements:

USE MyTable EXCLUSIVE
ALTER TABLE MyTable ALTER datefield D

If the dates are already in the proper format, or converted as RafM demonstrated.

If you are using earlier versions, it's not as straight forward as VFP.
You need to open the file exclusive and use MODIFY STRUCTURE to change it, or with ver. 2.5 or greater, use a SQL command:

SELECT field1, field2, ...., CTOD(datefld) as newdate, fieldn,;
FROM MyTable ;
INTO TABLE newtable

Or something similar.

alvalongo

04-15-04, 12:15

Hi,
Exactly what do yo want to do?
a) Are you trying to convert the data within the field to look like date or b) are you trying to convert the actual data type of the field from character type to date type?

Other questions:
c) Can you send a copy of the dbf file so I can help better?
d) In what development tool are you using this file?

bye

Alvaro
Bogota, Colombia (South America)

taxes

05-15-04, 19:24

Hi,

To clarify some of the earlier responses, if you are thinking about dBaseIII Plus, you can use the CTOD() function to convert a string to a date format provided that meaningful NUMBERS are separated by ANY non numeric character (I have not actually tried all non numerics but the 35 I have tried conform to this!). e.g. you can convert 25/4/2004, 25/04/2004, 25.4.2004 or 25a4b2004.
However, 25/4/04 converts to 25/04/1904.

If you want to convert a date format to a string then use

dtoc()