CODEWITHSUNDEEP
phpmysql
Eric
Eric

Reputation: 119

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 in 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

Upvotes: 0

Views: 3404

Answers (5)

Jose Vega
Jose Vega

Reputation: 10258

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

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

Upvotes: 0

ajreal
ajreal

Reputation: 47321

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}'";

Upvotes: 1

Cpt. eMco
Cpt. eMco

Reputation: 565

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')

Upvotes: 0

J V
J V

Reputation: 11936

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...

Upvotes: 0

benhowdle89
benhowdle89

Reputation: 37464

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!

Upvotes: 0

Related Questions