Name
GET_FORMAT()
Synopsis
GET_FORMAT(data_type
,standard
)
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 ...
Get MySQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.