Format dates stored in MySQL

MySQL stores dates and timestamps in one format only: YYYY-MM-DD hh:mm:ss. This is the format recommended by the International Organization for Standardization (ISO). It has the advantage of being unambiguous, and avoids the problems caused by different national conventions. For example, to an American 1/2/2010 means January 2, 2010, but to a European it means 1 February 2010. On the other hand, what's good for the database is not necessarily what you want to display in your application.

The answer is to use the MySQL DATE_FORMAT() function as part of your SQL query, and assign the result to an alias. The basic syntax looks like this:

DATE_FORMAT(column_name, 'format_string') AS new_name

Let's say you have a database column (field) called "registered" and you want to format the date as "January 2nd, 2010" (or whatever the actual date happens to be), use the following in your SQL query:

DATE_FORMAT(registered, '%M %D, %Y') AS reg_formatted

This converts the value in the registered column to the correct format, which appears as reg_formatted in the database results.

You can use any name for the alias, as long as it doesn't conflict with another column in the database or use one of the MySQL reserved words. As with all column names, the alias should not contain any spaces or hyphens, nor should it consist only of numbers.

Using a different name as the alias has the advantage that you can still use the original column name in an ORDER BY clause. If you use the original column name as the alias, the dates will be ordered with April first, followed by August - not exactly what you might be expecting.

To format the same date in European style (2 January 2010), use the following:

DATE_FORMAT(registered, '%e %M %Y') AS reg_formatted

Commonly used formatting specifiers

The formatting string used as the second argument to DATE_FORMAT() formats the date using specifiers that all begin with a percentage sign (%). Spaces and any other characters, including punctuation, are treated as literal characters. There's a full list of formatting specifiers in the MySQL online documentation. However, the most commonly used are as follows:

Year

%Y     Full year in four-digit format

%y     Last two digits of the year

Month

%M     Full name (January, February)

%b     First three letters (Jan, Feb)

%m     As a number with leading zero, if necessary

%c     As a number without leading zero

Day of month

%d     With leading zero

%e     No leading zero

%D     With English text suffix (1st, 2nd)

Weekday name

%W     Full name (Monday, Tuesday)

%a     First three letters (Mon, Tue)

Using DATE_FORMAT() in a SQL query

You use DATE_FORMAT() in a SELECT query like an ordinary column. So, let's say you have this existing query:

SELECT id, name, registered
FROM users
ORDER BY registered

To get the formatted date in your database result, amend the query like this:

SELECT id, name, 
DATE_FORMAT(registered, '%M %D, %Y') AS reg_formatted
FROM users
ORDER BY registered

If you're being lazy and using an asterisk to select all columns, you can add the column(s) you want to format to the SQL query like this (don't forget the comma after the asterisk):

SELECT *, DATE_FORMAT(registered, '%M %D, %Y') AS reg_formatted
FROM users
ORDER BY registered

If you're using the Dreamweaver Recordset dialog box, these changes need to be made with the dialog box in Advanced mode (click the Advanced button on the right).

The formatted date will be returned in the database results as reg_formatted (or whatever name you choose for the alias). In a Dreamweaver recordset, this will appear in the PHP code as $row_recordsetName['reg_formatted'], where recordsetName represents the name of your recordset.

IMPORTANT: There must be no space between DATE_FORMAT and the opening parenthesis. Technically, the format string can be enclosed in single or double quotes. However, when using the Dreamweaver Recordset dialog box, use only single quotes. This is because Dreamweaver wraps the SQL query in double quotes. Using double quotes within the SQL query generates a PHP parse error.