PHP and mySQL dates

Normally this is not an issue but a recent project I was working on meant that the normal way of using dates has caused an issue. When converting a date that is formatted dd/mm/yyyy to yyyy-mm-dd hh:mm:ss didn’t work the way I wanted it to. A way round this is to format the UK date in to the US date format then in to the mySQL datetime format and of course vice-versa on retrieving the date from the database.

Here’s how

I created 2 functions, php_to_mysql() and mysql_to_php

PHP to mySQL

function phptomysql($php)
{
//Break the date up in to an array
$php = explode("/", $php);
//Re=arrange the date in to the US format
$php = $php[1] . "/" . $php[0] . "/" . $php[2];
//Convert the date (string) in to php time format
$php = strtotime($php);
//Format the time value in to a php variable in the format of mySQL datetime field
$php = date('Y-m-d H:i:s', $php);
return $php;
}

mySQL to PHP

function mysqltophp($mysql)
{
//Change the mySQL date to a time value.
$mysql = strtotime($mysql);
//Format the time value to the UK date format
$mysql = date("d/m/Y", $mysql);
return $mysql;
}

This one is much simplier. As we do not need to involve a re-arrangment of the date from US to UK, we can just format the time value to the UK format straight off.

Example

View the date formatting in operation

One Response to:
“PHP and mySQL dates”

  1. Stewart Doxey March 24, 2011 at 6:12 pm #

    I imagined you would be able to do the first conversion using a conventional date(‘Y-m-d H:i:s’, strtotime(“dd/mm/yyyy”)) operation, but it turns out you can’t. Those dang American’s and their silly date formats!

    Thanks for sharing!