Table of Contents for

MySQL in a Nutshell, 2nd Edition

This function returns the format for a given data type, based on the standard given as the second argument. The format codes returned are the same codes used by the DATE_FORMAT() function. The data type may be DATE, TIME, DATETIME, or TIMESTAMP, and the format type may be EUR, INTERNAL, ISO, JIS, or USA. This function is available as of version 4.1.1 of MySQL. The TIMESTAMP data type isn’t acceptable until version 4.1.4.

Here’s an example using the function that returns the USA format:

SELECT GET_FORMAT(DATE, 'USA') AS 'US Format',
GET_FORMAT(DATE, 'EUR') AS 'European Format';

+-----------+-----------------+
| US Format | European Format |
+-----------+-----------------+
| %m.%d.%Y  | %d.%m.%Y        | 
+-----------+-----------------+

I wouldn’t say that using the period as the separator is very American, but the order of day followed by month is in keeping with American standards, and the day preceding the month is European. You can hand off the results of the function to DATE_FORMAT() to format the value of a date column like so:

SELECT appointment, 
DATE_FORMAT(appointment, GET_FORMAT(DATE, 'USA'))
AS 'Appointment'
WHERE apt_id = '8382';

+-------------+-------------+
| appointment | Appointment |
+-------------+-------------+
| 2008-03-15  | 03.15.2008  | 
+-------------+-------------+

Table 12-3 lists the results for the different combinations. The ISO standard refers to ISO 9075. The data type of TIMESTAMP is not listed because the results are the same as DATETIME.