Convert date formats between PHP and MySQL (mm/dd/yyyy to yyyy-mm-dd)

// September 8th, 2008 // CodeIgniter, PHP, Web Development

I have a view in HWC where I am using Javascript DHTML date picker script to pick the date and insert into test field. By the way HWC is the PHP-MVC (using codeigniter) application I am working on.

Date Picker in dhtml/Javascript

Date Picker in dhtml/Javascript

So as you see, the date pickers default format is mm/dd/yyyy,  which is not acceptable by MySQL date/time field as it is, so we need a little processing to make MySQL happy enough to store it ;)

Converting  mm/dd/yyyy to yyyy-mm-dd

$date=split("/",$_POST['date']);
// where the $_POST['date'] is a value posted by form in mm/dd/yy format
$dated=$date[2]."-".$date[0]."-".$date[1];
// The string dated is now in yyyy-mm-dd format
echo $dated;

For example, the date entered through form was 09/02/2009, after processing it will be stored in $dated as 2009-02-09. Pretty kool yeah? Just notice if you have / as I am happy if helps you. You need help with more similar stuff, post a comment.

One Response to “Convert date formats between PHP and MySQL (mm/dd/yyyy to yyyy-mm-dd)”

  1. Alan says:
    October 8, 2008 at 5:42 pm

    If you are processing US or EUR dates for insert into MySQL AND if you’re using a newer version (4.1.1 or later), you also have the str_to_date function:

    US:
    SELECT str_to_date(’09/08/2008′,’%m/%d/%Y’)

    EUR:
    SELECT str_to_date(’09/08/2008′,’%d/%m/%Y’)

    I think postgresql and Oracle have a similar, “to_date” function, but the formatting strings use a different syntax.

Leave a Reply