mysql converting text input to datetime field

I have two inputs Time and Date. I want to convert these fields to one using php and insert them into a datetime field i开发者_开发百科n mysql. I think I need to use a STR_TO_DATE. But I'm unsure how to do it. Thanks

Format Time = 12:00 PM

Date = 2010-11-17

Since you plan to use PHP, you can directly set into a format that MYSQL will accept,
such as

$the_date = date('Y-m-d H:i:s', strtotime($date.' '.$time);
$sql = "INSERT INTO YOUR_TABLE SET COL_FOR_DATE_TIME='{$the_date}'";

$Time = "12:00 PM";
$Date = "2010-11-17";
$DateTime = $Date . " " . $Time;

$timestamp = date('Y-m-d H:i:s',strtotime($DateTime));

This PHP code to get a start and end date:

$datef = (date("Y-m-d H-i-s",mktime($_POST['hour'], 0, 0, $_POST['month'], $_POST['day'], $_POST['year'])));
$datel = (date("Y-m-d H-i-s",mktime($_POST['hour1'], 0, 0, $_POST['month1'], $_POST['day1'], $_POST['year1'])));

This was posted from a load of html dropdowns!

I know this doesnt exactly answer your question but it will give you a good foundation!

You should use a timestamp, it's a much more easily manipulated standard with just as much granularity as you need for your current setup.

As for "Converting" the table, that's pretty much impossible, you would need to make a new table, move the stuff over, drop the old one and rename the new one...

The STR_TO_DATE function is indeed a good option.

You could try something like this:

STR_TO_DATE('2010-11-17 12:00 PM', '%Y-%m-%d %h:%i %p')