Here are versions in Access and FoxPro for converting character fields, such as "01021999" to a true date field, "01/02/ 1999."
No matter what program you're using, start by inserting a new field in your table. In this example, the original date field is called DOB and the newly created field is called BIRTHDT. The date appears as mmddyyyy.
In Access:
In the grid view, put the name of the new field, BIRTHDT, in the field grid. In the criteria line: Left([DOB],2) & "/" & Mid([DOB],3,2 & "/" & Mid([DOB],5,4)
The SQL version looks like this: UPDATE [TABLENAME] SET BIRTHDT= Left([DOB],2) & "/" & Mid([DOB],3,2)
& "/" & Mid([DOB],5,4)
This tells the computer, start at the left side of the DOB field and take two digits, then insert a forward slash (/), then go to the middle of the DOB field and start at the third position and take two digits, then insert another forward slash and finally go to the middle of the DOB field and start at the fifth position, taking four digits.
If your date has only two digits for the year, replace the final 4 with a 2.
Make sure to use the ampersand (&) and not the plus sign.
In FoxPro:
Here's the SQL (notice slightly different syntax): Replace all BIRTHDT with; CTOD(substr(D0B,1,2) + "/" + substr(DOB,3,2) + "/" substr(D0B,5,4))