how i can Split the date ?

Posted on 2008-11-18
Last Modified: 2012-05-05

i have an old Mysql Database that conatins the Data colums in that was store the date in one filed .  i want to transfer all data to the new database that store the date as the day , month and year separat every one in unique column ..
how i can convart the old date to the new one ?
Question by:palserv
    LVL 8

    Accepted Solution

    list($day, $month, $year) = split('[/.-]', $date);

    CmnFns::formatDate( mktime(0,0,0,$day,$month,$year) );

    To display it, just use php's built in stuff:

    date('m-d-Y', $data['member_date']);

    Putting it in the database, do this:

    $d = explode('-', $_POST['member_date']);
    $date = mktime(0,0,0,$d[0],$d[1],$d[2]);
    LVL 27

    Assisted Solution

    Assuming the Data field contains the standard MySQL Date format, you can

    SELECT SUBSTRING(Data,1,4) as Yr, SUBSTRING(Data,6,2) as Mon, SUBSTRING(Data,9,2) as Dy FROM Table

    LVL 19

    Assisted Solution

    Storing the date in one column is GOOD!  I strongly recommend you keep it that way.

    MySQL lets you EASILY extract parts of the date with simple functions.  Suppose the column is named some_date.  You could write this query:

    SELECT YEAR(some_date), MONTH(some_date), DAY(some_date) FROM your_table;

    Now you've selected the year, month, and day as separate columns in the RESULT, even though they're stored in a single date column in the table.  The main advantage of having them in the same column is you can write queries like...

    SELECT stuff FROM your_table WHERE some_date BETWEEN '2008-01-03' AND '2008-02-06';

    That query would be hard to write with the date split up!  You'd have to write:

    SELECT stuff FROM your_table WHERE some_date_year = 2008 AND ((some_date_month = 1 AND some_date_day >= 3) OR (some_date_month = 2 AND some_date_day <= 6));


    Having said that, if you really, absolutely, positively must separate the date into different columns, you can let the database do the work one more time:

    UPDATE your_table SET some_date_month = MONTH(some_date), some_date_day = DAY(some_date), some_date_year = YEAR(some_date);
    LVL 39

    Expert Comment

    Can I ask you why you would want to do that?
    LVL 27

    Expert Comment

    I agree with Votey and Brandon -- if you can, stick with the standard MySQL date format of yyyy-mm-dd.  You can always pick out Year, Month, Day when you need it, in exactly the way I showed above.  

    You will also be able to sort, use date ranges, and other selection functions MUCH more easily if you keep the standard format.
    LVL 39

    Expert Comment

    Plus, what would prevent the storage of 2008, 06, 40 as yyyy,mm,dd.  You have no internal data validation.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Suggested Solutions

    Title # Comments Views Activity
    Reverse asset logic not working 11 28
    Oracle Pivot 2 20
    How to represnt 52 selections 1 22
    MySQL - Limit or Top Records 15 21
    Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
    These days socially coordinated efforts have turned into a critical requirement for enterprises.
    Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
    This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

    760 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

    12 Experts available now in Live!

    Get 1:1 Help Now