MySQL in a Nutshell

Name

GET_FORMAT( )

Synopsis

GET_FORMAT(data_type, format_type)

This function returns the format for a format type given as the second argument for a datatype given as the first. The format codes returned are the same codes used by the DATE_FORMAT( ) function. Four datatypes may be given: DATE, TIME, DATETIME, and TIMESTAMP. Five format types may be given as the second argument: EUR, INTERNAL, ISO, JIS, and USA. This function is available as of Version 4.1.1 of MySQL. The TIMESTAMP datatype 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');
+-------------------------+
| GET_FORMAT(DATE, 'USA') |
+-------------------------+
| %m.%d.%Y                |
+-------------------------+

You can hand off to the DATE_FORMAT( ) function for formatting the value of a date column like so:

SELECT DATE_FORMAT(appointment, GET_FORMAT(DATE, 'USA'))
          AS Appointment LIMIT 1;
+-------------+
| Appointment |
+-------------+
| 01.11.2005  |
+-------------+

Table 6-3 lists the results for the different combinations. The datetype of TIMESTAMP is not listed, because the results are the same as DATETIME.

Table 6-3. DATE_FORMAT arguments and their results

Combination	Results
DATE, 'EUR'	%d.%m.%Y
DATE, 'INTERNAL'	%Y%m%d
DATE, 'ISO'	%Y-%m-%d
DATE, 'JIS'	%Y-%m-%d
DATE, 'USA'	%m.%d.%Y
TIME, 'EUR'	%H.%i.%S
TIME, 'INTERNAL'	%H%i%s
TIME, 'ISO'	%H:%i:%s
TIME, 'JIS'	%H:%i:%s
TIME, 'USA'	%h:%i:%s %p
DATETIME, 'EUR'	%Y-%m-%d-%H.%i.%s