Dates and times are stored in MySQL in the format "YYYY-MM-DD" and "YYYY-MM-DD HH:MM:SS" which is not necessarily the format you want to display in your web page or application. There are two methods to reformat the date and time into the desired format. One is to do it in the SQL query in MySQL calling the DATE_FORMAT() function and the other is to do it with the programming language retrieving the data from the MySQL database. The example used in this article to do this uses PHP.
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:
SELECT DATE_FORMAT(CURDATE(), '%d/%m/%Y') OR SELECT DATE_FORMAT(CURDATE(), '%m/%d/%Y')
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:
SELECT DATE_FORMAT(column_name, '%d/%m/%Y') FROM tablename OR SELECT 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 the MySQL 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().
$res = mysql_query('SELECT datefield FROM tablename WHERE foo = "bar"');
$row = mysql_fetch_array($res);
print 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.
The full list of possible date and time formatting placeholders is available in the date manual page.