PHP - Convert string date and time to MySQL DateTime

[PloMar]

I have two text fields, one for the date in "08/21/2012" format and a second field containing the time in the format "09:41am".
How can I end up converting to DATETIME (2012-08-21 09:41:00)?
Then, how to convert back out of it into a "08/21/2012" and "09:41am" format?

On the database side, you can use:
- STR_TO_DATE() to convert into a database friendly format.

Code: Select all

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

- DATE_FORMAT() will then return whatever part you want of that database date / time.

Code: Select all

SELECT DATE_FORMAT( '2012-21-08 09:41:00', '%m/%d/%Y' ) AS gd, DATE_FORMAT( '2012-21-08 09:41:00', '%r' ) AS gt ...
/* Result: gd: 08/01/2012, gt: 09:41:00am */

On the PHP side you can use:
- strtotime() to parse about any English textual datetime description into a Unix timestamp.
- date() - Format a local time/date.

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

Code: Select all

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

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

echo date('m/d/Y H:ia', $stamp);  // 08/21/2012 09:41am