PHP Converting string date and time to MySQL DateTime [duplicate]

Question

I have two text fields, one for the date in 08/01/2012 format and a second field containing the time. I currently have the time field in the format 09:41am but I have some flexibility with it's format (if 24hr is easier for example).

I was planning on just concatenating the strings and then converting. Should I convert the date to 2012-08-01 first?

How can I end up converting to datetime (2012-08-01 09:41:00)? How to convert back out of it into a 08/01/2012 and 09:41am format?

See [strtotime](php.net/manual/en/function.strtotime.php) and [date](php.net/manual/en/function.date.php). Concat, strtotime, then date into whatever format you want. — Palladium
– Palladium, Commented Aug 1, 2012 at 16:50
So, you are currently storing date and time as plain strings …? — knittl
– knittl, Commented Aug 1, 2012 at 16:50
no, I'm storing as datetime, but I need to show date and time in separate fields on a page. The date has to be in a certain format to work with a jQuery datepicker. — Motive
– Motive, Commented Aug 1, 2012 at 16:54

juergen d · Accepted Answer · 2012-08-01 16:52:21Z

6

SELECT STR_TO_DATE(concat('08/01/2012', '09:41am'),'%d/%m/%Y%h:%i');

answered Aug 1, 2012 at 16:52

juergen d

205k40 gold badges305 silver badges377 bronze badges

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

1 Comment

Motive Over a year ago

Perfect, and SELECT DATE_FORMAT( '2012-01-08 09:41:00', '%r' ) to convert back seems to work too. thanks!

Kermit · Accepted Answer · 2012-08-01 17:00:40Z

2

On the database side, you can use:

STR_TO_DATE() using the specifiers in this table to convert into a database friendly format. Reference

DATE_FORMAT() will then return whatever part you want of that database time. Reference

On the PHP side you can use:

strtotime() - Parse about any English textual datetime description into a Unix timestamp
date - Format a local time/date

strotime will produce a Unix timestamp based on any string date/time that is passed to it. If you pass both your fields (08/01/2012 & 09:41am) it will produce a timestamp based on it. To reverse the process, you use date("m/d/Y H:ia").

$field1 = '08/01/2012';
$field2 = '09:41am';

$stamp = strtotime($field1 . ' ' . $field2);

echo date("m/d/Y H:ia", $stamp);

I have not tested this, but it should work.

edited Aug 1, 2012 at 17:00

answered Aug 1, 2012 at 16:55

Kermit

34.1k13 gold badges89 silver badges121 bronze badges

Collectives™ on Stack Overflow

PHP Converting string date and time to MySQL DateTime [duplicate]

2 Answers 2

1 Comment

Comments

Hot Network Questions