how i can Split the date ?

Hello

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 ?
palservAsked:
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]);
0
 
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

0
 
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));

Yuck.


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);
0
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.

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