Database Reference
In-Depth Information
Adjusting to Standards and Time Zones
There a few standardsfor formatting the date and time. For instance, the last day of
December and the year could be written numerically as
12-31-2014
or
31-12-2014
. Which
standard you will use on a server may be based on where you're located in the world, or
your employer and client preferences, or some other factor. To get the date format for a par-
ticular standard, you canuse
GET_FORMAT()
. Enter the following to try this:
SELECT GET_FORMAT(DATE, 'USA');
+-------------------------+
| GET_FORMAT(DATE, 'USA') |
+-------------------------+
| %m.%d.%Y |
+-------------------------+
As the name implies,
GET_FORMAT()
checks for a particular place or locale and returns
the string that can be used in
DATE_FORMAT()
to produce the desired format. It might be
a bit surprising that the U.S. format uses periods instead of hyphens to separate elements of
the date. In
GET_FORMAT
, the first argument indicates whether you want the date, the
time, orboth (i.e.,
DATE
,
TIME
, or
DATETIME
). The second argument specifies the date
or time standard, and can be one of the following:
▪
EUR
for Europe
▪
INTERNAL
for the format in which time is stored, without punctuation
▪
ISO
for ISO 9075 standard
▪
JIS
for Japanese Industrial Standard
▪
USA
for United States
The
ISO
standard (
yyyy-mm-dd hh:mm:ss
) is the default for displaying the date and
time in MySQL.
Enter this simple example that uses
GET_FORMAT()
:
SELECT GET_FORMAT(DATE, 'USA'), GET_FORMAT(TIME, 'USA');
+-------------------------+-------------------------+
| GET_FORMAT(DATE, 'USA') | GET_FORMAT(TIME, 'USA') |
+-------------------------+-------------------------+
| %m.%d.%Y | %h:%i:%s %p |
+-------------------------+-------------------------+
