[Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 805
  • Last Modified:

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 ?
3 Solutions
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]);
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);

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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.

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now