GET_FORMAT( )

This MySQL 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.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

GET_FORMAT(data_type, standard)

Explanation

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.

Examples

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 keeping with American standards, and day preceding 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  | 
+-------------+-------------+

The table below 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.

  • 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

  • DATETIME, 'INTERNAL'
  • %Y%m%d%H%i%s

  • DATETIME, 'ISO'
  • %Y-%m-%d %H:%i:%s

  • DATETIME, 'JIS'
  • %Y-%m-%d %H:%i:%s

  • DATETIME, 'USA'
  • %Y-%m-%d-%H.%i.%s