The standard date separators are "/" and "-" I believe.
I've just recently completed a 5.5 to 10 transition myself. Feel free to ask about any other problems you've encountered during the conversion.
I wonder if you could strip out all the problematic date separators if you defined a new date field and used Replace field contents to copy the original date field contents into the new field. If that worked, you could then use a second Replace to copy them back.
If that didn't work, I'd use replace to copy the dates to and from a number field to strip out the formatting.
What are the standard date separators?
The standard date separators are (1) whatever your OS is using as the date separator for the short date format (unless you are using a file created on another system, and it's set to use its own stored formats), and (b) a forward slash / (this is always recognized as a valid separator).
I'd suggest you convert the dates in the source version. It's been a while, but a calculation field (result is Date) like =
Int ( Datefield )
should normalize all entries to a common separator.
Thanks. Sounds like it is safest to use the forward slash. But it seems to me that I first need to put all the records with faulty date formats into a found set so I can see whether what I try works or not. Any ideas on how to find them? Can I write a script that searches fields, and if they are date fields then applies the right format to the data already there?
Use Comment's suggestion. (Mine works, but his uses fewer steps.)
You could do it this way:
Find all your records
Click in the date field
Selecte Replace Field Contents
Specify a calculation and enter Int(yourdatefield)
Do the replace.
This should strip out all the user entered formatting and revert everything back to standard delimitters.
If you use a calculation field, it will convert all dates - and you'll still have the original for comparison.
Once you are satisified with the result, you can change the calculation field to type Date, and delete the original date field (provided the calculation was a stored one). Be sure to have a backup in any case.
Thanks. In case someone is looking to do something similar, a decription of what I ended up doing may be helpful (using comment's suggestion of using calculation fields rather than replacing seemed to give me a little more of a backup). Here are the steps I took:
- Duplicate all date fields.
- Change duplicated date fields to calculation, [date field], date type.
- OK. Let it process the changes.
- Define fields: Change date field copies to date.
- Copy over options from date fields to date field copies (or write them down).
- Change date fields to calculation, [date field copy], date type.
- OK. Let it process.
- Define fields: change date fields back to date, and reconstruct options.
- For some reason, it needs to process again.
- If it's OK, delete date field copies that you made in step 1.