Converting SQL date in PHP to European date format and vice-versa

: Joel Lipman; Personal Home Page; 17 September 2009; Hits: 6170

The Issue

If you've ever made PHP scripts to process data within a LAMP environment (Linux, Apache, MySQL, PHP) then this happens a lot. In the following example, our HTML form will allow the user to specify a date (so excludes hours, minutes and seconds). For demonstration purposes, I'm going to be using the European date format so DD/MM/YYYY.

The Solution

This is what I use to convert a given SQL date to the standard European format:

copy raw

$this_date=mysql_result(mysql_query("SELECT my_date_field FROM my_table WHERE my_id='$this_id'"), 0, "my_date_field"); 
$this_date_array=explode("-", trim(substr($this_date, 0, strpos($this_date, " ")))); 
$this_time=trim(substr($this_date, strpos($this_date, " ")+1)); 
$formatted_datetime=implode("/", array_reverse($this_date_array))." ".$this_time;

$this_date=mysql_result(mysql_query("SELECT my_date_field FROM my_table WHERE my_id='$this_id'"), 0, "my_date_field");
$this_date_array=explode("-", trim(substr($this_date, 0, strpos($this_date, " "))));
$this_time=trim(substr($this_date, strpos($this_date, " ")+1));
$formatted_datetime=implode("/", array_reverse($this_date_array))." ".$this_time;

Replace the variable names and the SQL column and table names as per your setup.

Converts 2001-06-25 09:41:00 to 25/06/2001 09:41:00

And Vice-Versa:

copy raw

$posted_datetime=$_POST['this_datetime'];
$posted_datetime_array=explode(" ", trim($posted_datetime));
$posted_time=trim($posted_datetime_array[1]);
$posted_date_array=explode("/", trim($posted_datetime_array[0]));
$formatted_posted_datetime=implode("-", array_reverse($posted_date_array))." ".$posted_time;

$posted_datetime=$_POST['this_datetime'];
$posted_datetime_array=explode(" ", trim($posted_datetime));
$posted_time=trim($posted_datetime_array[1]);
$posted_date_array=explode("/", trim($posted_datetime_array[0]));
$formatted_posted_datetime=implode("-", array_reverse($posted_date_array))." ".$posted_time;