MySQL stores dates in one format only, the ISO-recommended YYYY-MM-DD HH:MM:SS. Any attempt to store dates in another format will result in the date and time being converted to 0000-00-00 00:00:00.
This is not as inconvenient as it sounds, because MySQL has a large number of date and time functions.
To format a MySQL date in a more human readable format, use the DATE_FORMAT() function. This takes as its first argument the name of the column that contains the date you want to format. The second argument is a string containing the formatting characters (you can find a full list by following the link at the beginning of this paragraph). Assign the result of the DATE_FORMAT() function to an alias, using the AS keyword.
The following examples assume you want to format the value stored in a column called start_date. The formatted date is assigned to an alias called start_formatted, which is how it appears in a recordset.
To format start_date as Thursday, June 18, 2009:
SELECT col1, col2, col3, DATE_FORMAT(start_date, '%W, %M %e, %Y') AS start_formatted FROM mytable
To format start_date as Thu, 18th Jun 2009:
SELECT col1, col2, col3, DATE_FORMAT(start_date, '%a, %D %b %Y') AS start_formatted FROM mytable
When using DATE_FORMAT() - or any other MySQL function - make sure there is no space between the function name and the opening parenthesis. The following example WILL NOT WORK:
SELECT col1, col2, col3, DATE_FORMAT (start_date, '%a, %D %b %Y') AS start_formatted FROM mytable
Also make sure you use single quotes around the format string passed as the second argument to DATE_FORMAT().
Europe, Middle East and Africa