MySQL DATE_FORMAT() AND PHP date() formats

I’m always finding myself looking up strings to format PHP and MySQL dates. A simple d/m/Y is pretty straight forward but sometimes things can get a bit fiddly. Where ever possible I try and leave my date formatting in the database, but I still find myself doing it in PHP all the time.

Here are a few simple cut and paste date formats.

PHP Date MySQL Date PHP Format MySQL Format Notes
7/9/2009 7/9/2009 j/n/Y %e/%c/%Y
07/09/2009 07/09/2009 d/m/Y %d/%m/%Y
7/9/2009 8:07 7/9/2009 8:07 j/n/Y G:i %e/%c/%Y %k:%i
7/9/2009 8:07 AM 7/9/2009 8:07 AM j/n/Y G:i A %e/%c/%Y %k:%i %p
07/09/2009 8:07 AM 07/09/2009 8:07 AM d/m/Y G:i A %d/%m/%Y %k:%i %p
07/09/2009 08:07 AM 07/09/2009 08:07 AM d/m/Y H:i A %d/%m/%Y %H:%i %p
07/09/2009 8:07 am d/m/Y G:i a
Mon 7th Sep 2009 Mon 7th Sep 2009 D jS M Y %a %D %b %Y
Monday 7th September 2009 Monday 7th September 2009 l jS F Y %W %D %M %Y
2009-09-07T08:07:50+01:00 Monday 7th September 2009 Y-m-d\TH:i:sP %W %D %M %Y PHP DATE_ATOM constant
Mon, 07 Sep 2009 08:07:50 +0100 Mon, 07 Sep 2009 08:07:50 D, d M Y H:i:s O %a, %d %b %Y %T PHP DATE_RSS constant

The PHP way

 
echo(date('d/m/Y', strtotime('2009-09-07 08:07:50')));
 
//or
 
$dateTime = new DateTime("now");
echo $dateTime->format("Y-m-d H:i:s")

The MySQL way

 
$sqlstring = "SELECT DATE_FORMAT('2009-09-07 08:07:50', '%d/%m/%Y') AS formated_date FROM some_table" ;

If you haven’t looked at the PHP manual closely for a while you should definitely (re)acquaint yourself with the DateTime Class.