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

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:

 

copyraw
$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;
  1.  $this_date=mysql_result(mysql_query("SELECT my_date_field FROM my_table WHERE my_id='$this_id'"), 0, "my_date_field")
  2.  $this_date_array=explode("-", trim(substr($this_date, 0, strpos($this_date, " "))))
  3.  $this_time=trim(substr($this_date, strpos($this_date, " ")+1))
  4.  $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:

copyraw
$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;
  1.  $posted_datetime=$_POST['this_datetime']
  2.  $posted_datetime_array=explode(" ", trim($posted_datetime))
  3.  $posted_time=trim($posted_datetime_array[1])
  4.  $posted_date_array=explode("/", trim($posted_datetime_array[0]))
  5.  $formatted_posted_datetime=implode("-", array_reverse($posted_date_array))." ".$posted_time

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

 
 

 

Category: Personal Home Page :: Article: 178

Add comment

Your rating:

Submit

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Accreditation

Badge - Zoho Creator Certified Developer Associate
Badge - Zoho Deluge Certified Developer
Badge - Certified Zoho CRM Developer

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF

Please publish modules in offcanvas position.