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.


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!