HOWTO: Convert String Date Format to Date Type in FoxProID: Q126698
|
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.
*** 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.
Additional query words: spelled alter
Keywords : kbcode FoxWin FxprgGeneral KBQ
Version : WINDOWS:2.6a
Platform : WINDOWS
Issue type : kbhowto
Last Reviewed: April 26, 1999