convert character string to date in dbase

Posted on 2009-04-22
Medium Priority
Last Modified: 2013-11-24
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.
Question by:revo1059
  • 3
LVL 43

Expert Comment

ID: 24206370
What dBase engine version do you use? Hope it is not dBase III but something new.

Author Comment

ID: 24206444
It is dBASEIII. It's an old application that I need to convert some data for.

LVL 43

Accepted Solution

pcelba earned 2000 total points
ID: 24206471
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.
LVL 43

Expert Comment

ID: 24206549
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.

Expert Comment

ID: 24207024
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


Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question