how i can Split the date ?


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 ?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Haris VCommented:
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]);

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
Cornelia YoderArtistCommented:
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

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);
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

Can I ask you why you would want to do that?
Cornelia YoderArtistCommented:
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.
Plus, what would prevent the storage of 2008, 06, 40 as yyyy,mm,dd.  You have no internal data validation.
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

From novice to tech pro — start learning today.