How to Convert String Date Format to Date Type in FoxPro

Last reviewed: March 20, 1998
Article ID: Q126698

2.60a WINDOWS kbprg kbcode

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 reference words: FoxWin 2.60a spelled alter
KBCategory: kbprg kbcode
KBSubcategory: FxprgGeneral

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: March 20, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.