Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2003
    Posts
    14

    Question Unanswered: convert a field in DBF files

    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???

  2. #2
    Join Date
    Apr 2004
    Posts
    5

    Re: convert a field in DBF files

    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]

  3. #3
    Join Date
    Dec 2003
    Posts
    104
    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.
    DSummZZZ

    Even more Fox stuff at
    www.davesummers.net/foxprolinks.htm

  4. #4
    Join Date
    Jan 2004
    Location
    Bogota, Colombia (South America)
    Posts
    3

    Question

    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)

  5. #5
    Join Date
    Dec 2003
    Location
    Bristol, England
    Posts
    29
    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()
    taxes

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •