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?
Haris VConnect With a Mentor Commented:
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]);
Cornelia YoderConnect With a Mentor ArtistCommented:
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

VoteyDiscipleConnect With a Mentor Commented:
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);
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.