mysql converting text input to datetime field

Question

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

ajreal · Accepted Answer · 2010-11-17 14:29:59Z

1

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

answered Nov 17, 2010 at 14:29

ajreal

47.4k11 gold badges97 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jose Vega · Accepted Answer · 2010-11-17 14:32:18Z

0

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

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

answered Nov 17, 2010 at 14:32

Jose Vega

10.3k7 gold badges43 silver badges58 bronze badges

Comments

benhowdle89 · Accepted Answer · 2010-11-17 14:23:13Z

0

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!

answered Nov 17, 2010 at 14:23

benhowdle89

37.6k74 gold badges208 silver badges342 bronze badges

Comments

J V · Accepted Answer · 2010-11-17 14:25:05Z

0

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

answered Nov 17, 2010 at 14:25

J V

12.1k10 gold badges56 silver badges78 bronze badges

2 Comments

Eric Over a year ago

Timestamp does not work this is not a by entry form its based off the premise of input fields and people filling them out Timestamp automatically generates a time and date but the time and date the entry is put into the database is not what Im looking for. Also Im not trying to convert the entire table im converting input fields using php to then put data into a mysql database.

J V Over a year ago

Timestamp is a format, you can take user input and put it in a timestamp and without the problems you will get without it... The way you are doing it is like storing a users first and last names in the same field because you can't be bothered looking at the benefits of splitting them up.

Cpt. eMco · Accepted Answer · 2010-11-17 14:26:12Z

0

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

answered Nov 17, 2010 at 14:26

Cpt. eMco

5654 silver badges13 bronze badges

2 Comments

Eric Over a year ago

since I have input variables how would I use them inside STR_TO_DATE?

Cpt. eMco Over a year ago

If you're using php, like this: $query = "INSERT INTO table (date) VALUES(STR_TO_DATE('" . $date . "', '%Y-%m-%d %h:%i %p');"

Collectives™ on Stack Overflow

mysql converting text input to datetime field

5 Answers 5

Comments

Comments

Comments

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related