The MySQL DATE_FORMAT() Function

If you wanted to change the format of a MySQL date from YYYY-MM-DD to MM/DD/YYYY or DD/MM/YYYY format you can use the DATE_FORMAT() function as shown in the example below:


The first example above would return the current date formatted in dd/mm/yyyy format (ie day then month then year) and the second in mm/dd/yyyy format (ie month then day then year). To select a column from a table you would run the query like so:

1SELECT DATE_FORMAT(column_name, '%d/%m/%Y') FROM tablename
3SELECT DATE_FORMAT(column_name, '%m/%d/%Y') FROM tablename

The exact format used is entirely up to you and can include month and day numbers with or without leading zeros, short month names, long month names, hours, minutes, seconds etc etc. Refer to theMySQL manual page for a complete list of formatting elements.

The advantage of using MySQL to output the format in the desired format is that if you change programming languages for your application you don't have to worry about working out how to format the result in your new language.

Formatting the data and time programatically with PHP

Instead of reformatting the date and/or time format in the MySQL query you can do it programatically in your web page or application. This example uses PHP, which has two extremely useful built-in functions for dealing with dates and times: strtotime() and date().

1$res = mysql_query('SELECT datefield FROM tablename WHERE foo = "bar"');
2$row = mysql_fetch_array($res);
3print date('d M Y', strtotime($row['datefield']));

The strtotime() function is used to parse the date and time returned from the MySQL query into a UNIX timestamp. The date() function then formats that returned timestamp into 'd M Y' format, which is the day of the month with a leading zero, followed by the short month name (eg 'Jan', 'Feb', 'Mar') then a 4 digit year.


Popular Posts