HOWTO: Convert String Date Format to Date Type in FoxPro

ID: Q126698

The information in this article applies to:

Microsoft FoxPro for Windows, version 2.6a

SUMMARY

A number of spreadsheet applications output dates in a "Month Day, Year" format such as "January 1, 1994." This renders the dates useless when appended into a FoxPro database that requires the field be a date type. The following code shows how to convert the string to a character date, which is an accepted format for standard date type conversion.

MORE INFORMATION

*** MDY2DATE.PRG converts string Month Day, Year to char date


   *
   *   USAGE: mdate=MDY2DATE("January 1, 1994")
   *
   *   NOTE: This function does not do error checking to ensure the date
   *   entered is valid. However, a quick check of what's returned with the
   *   CTOD(), would accomplish this. If invalid, {  /  /  } is returned.
   *

   PARAMETER fld
   mo = UPPER(LEFT(fld,3))
   mth = IIF(mo='JAN','1', IIF(mo='FEB','2', IIF(mo='MAR','3', ;

         IIF(mo='APR','4', IIF(mo='MAY','5', IIF(mo='JUN','6', ;
         IIF(mo='JUL','7', IIF(mo='AUG','8', IIF(mo='SEP','9', ;
         IIF(mo='OCT','10',IIF(mo='NOV','11','12')))))))))))+'/'

   RETURN mth + SUBSTR(fld, AT(' ',fld) + 1, (AT(',',fld)-1) ;

         - AT(' ',fld)) + '/' + SUBSTR(fld, AT(' ',fld,2) + 1)

From the Command window, enter the following:


   REPLACE ALL cdate WITH mdy2date(cdate)

Once the all the date fields have been filled, the database structure must be modified. From the Database menu, choose Setup. Then select Modify, or enter the following in the Command window:


   MODIFY STRUCTURE

Select the character string date field, and change the field type from Character type to Date type. Save the changes to the file.

By default, only the last two digits of the year will be displayed. To display the full year, use the SET CENTURY ON command.

Additional query words: spelled alter


Keywords          : kbcode FoxWin FxprgGeneral KBQ 
Version           : WINDOWS:2.6a
Platform          : WINDOWS 
Issue type        : kbhowto