convert character string to date in dbase

In a DBASEIII DBFI have a field that has date info in it (ex. 03112010) and I need to convert field to a date field and keeping the data intact (so 3112010 would then show as 03/11/2010).

So let's say the existing field is called DATE (but it's a character field), I need to be able to make DATE a date field and the data in it convert to date data or move the data to a properly formatted field and convert it.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

What dBase engine version do you use? Hope it is not dBase III but something new.
revo1059Author Commented:
It is dBASEIII. It's an old application that I need to convert some data for.

To convert character field to date use the CTOD() function. If your date format is fixed "mmddyyyy" (always 8 characters) then you may use following conversion:

*-- The NewDate is a new column of Date data type
REPLACE ALL NewDate WITH CTOD(LEFT(date,2)+'/'+SUBSTR(date,3,2)+'/'+RIGHT(date,4))

If the character date format is different you have to use different settings or formula.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
After the conversion you may remove the old date column and then rename the NewDate column. Some application changes will be probably necessary if you change the column type.

If the original date column is longer than 8 characters then above formula needs some updates.
I can add little more, pcelba is exactly right.
The stuff inside the CTOD() can obviously be adjusted to cope with dates in any format you like

I presume the application works just fine without the new field so rather than risk an upset, you should consider keeping the two date fields (you will obviously need to add some code wherever the date is entered/edited to maintain the new field).

Depending on the size of the application this may well be a less time consuming approach than tracking down all the occurrences of the character date field and changing the usage to reflect the new data type

Good luck

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.