convert character string to date in dbase

Posted on 2009-04-22
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
    LVL 41

    Expert Comment

    What dBase engine version do you use? Hope it is not dBase III but something new.
    LVL 1

    Author Comment

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

    LVL 41

    Accepted Solution

    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 41

    Expert Comment

    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.
    LVL 3

    Expert Comment

    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

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
    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 simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now