You can’t. However, you can rewrite non-ISO input values into
ISO format when storing dates, and you can rewrite ISO values to other
formats for display by using the
DATE_FORMAT()
function.
The CCYY-MM-DD
format that MySQL uses for DATE
values follows the ISO 8601 standard
for representing dates. Because the year, month, and day parts have a
fixed length and appear left to right in date strings, this format has
the useful property that dates sort naturally into the proper temporal
order. Chapters 7 and 8 discuss ordering and grouping
techniques for date-based values.
ISO format, although common, is not used by all database
systems, which can cause problems if you want to move data between
different systems. Moreover, people commonly like to represent dates
in other formats such as MM/DD/YY
or
DD-MM-CCYY
. This too can be a source of
trouble, due to mismatches between human expectations of what dates
should look like and the way that MySQL actually represents
them.
A question frequently asked by people who are new to MySQL is,
“How do I tell MySQL to store dates in a specific format such as
MM/DD/CCYY
?” That’s the wrong
question. The right question is, “If I have a date in a specific
format, how can I store it in MySQL’s supported format, and vice
versa?” MySQL always stores dates in ISO format, a fact that
has implications both for data entry and for processing retrieved
query results:
For data-entry purposes, to store values that are not in ISO
format, you normally must rewrite them first. If you don’t want to
rewrite your dates, you need to store them as strings (for
example, in a CHAR
column). But
then you can’t operate on them as dates.
Chapter 10 covers the topic of date
rewriting for data entry. That chapter also discusses checking
dates to verify that they’re valid. In some cases, if your values
are close to ISO format, rewriting may not be necessary. For
example, MySQL interprets the string values 87-1-7
and 1987-1-7
and the numbers 870107
and 19870107
as the date 1987-01-07
when they are loaded into a
DATE
column.
For display purposes, you can rewrite dates to non-ISO
formats by using the DATE_FORMAT()
function. It
provides a lot of flexibility for changing date values into other
formats (see later in this section). You can also use functions
such as YEAR()
to extract
parts of dates for display (Extracting Parts of Dates or Times). Additional discussion can be
found in Chapter 10.
When you enter date values, one way to rewrite non-ISO
dates is to use the STR_TO_DATE()
function. STR_TO_DATE()
takes a string
representing a temporal value and a format string that specifies the
“syntax” of the value. Within the formatting string, you
use special sequences of the form %
c
, where
c
specifies which part of the date to
expect. For example, %Y
, %M
, and %d
signify the four-digit year, the month
name, and the two-digit day of the month. To insert the value May 13, 2007
into a DATE
column, you can do this:
mysql>INSERT INTO t (d) VALUES(STR_TO_DATE('May 13, 2007','%M %d, %Y'));
mysql>SELECT d FROM t;
+------------+ | d | +------------+ | 2007-05-13 | +------------+
For date display, MySQL uses ISO format
(CCYY-MM-DD
) unless you tell it otherwise.
If you want to display dates or times in a format other than what
MySQL uses by default, use the DATE_FORMAT()
or TIME_FORMAT()
functions to rewrite them. If you require a more specialized
format that those functions cannot provide, write a stored
function.
To rewrite date values into other formats, use the DATE_FORMAT()
function, which takes
two arguments: A DATE
, DATETIME
, or TIMESTAMP
value, and a string describing how
to display the value. The format string uses the same kind of
specifiers as STR_TO_DATE()
.
The following statement shows the values in the date_val
table, both as MySQL displays them
by default and as reformatted with DATE_FORMAT()
:
mysql>SELECT d, DATE_FORMAT(d,'%M %d, %Y') FROM date_val;
+------------+----------------------------+
| d | DATE_FORMAT(d,'%M %d, %Y') |
+------------+----------------------------+
| 1864-02-28 | February 28, 1864 |
| 1900-01-15 | January 15, 1900 |
| 1987-03-05 | March 05, 1987 |
| 1999-12-31 | December 31, 1999 |
| 2000-06-04 | June 04, 2000 |
+------------+----------------------------+
DATE_FORMAT()
tends to
produce rather long column headings, so it’s often useful to provide
an alias to make a heading more concise or meaningful:
mysql>SELECT d, DATE_FORMAT(d,'%M %d, %Y') AS date FROM date_val;
+------------+-------------------+
| d | date |
+------------+-------------------+
| 1864-02-28 | February 28, 1864 |
| 1900-01-15 | January 15, 1900 |
| 1987-03-05 | March 05, 1987 |
| 1999-12-31 | December 31, 1999 |
| 2000-06-04 | June 04, 2000 |
+------------+-------------------+
The MySQL Reference Manual provides a
complete list of format sequences to use with DATE_FORMAT()
, TIME_FORMAT()
, and STR_TO_DATE()
. Some of the more
commonly used ones are shown in the following table:
The time-related format sequences shown in the table are useful
only when you pass DATE_FORMAT()
a value that has both
date and time parts (a DATETIME
or
TIMESTAMP
). The following statement
demonstrates how to display DATETIME
values from the datetime_val
table using formats that
include the time of day:
mysql>SELECT dt,
->DATE_FORMAT(dt,'%c/%e/%y %r') AS format1,
->DATE_FORMAT(dt,'%M %e, %Y %T') AS format2
->FROM datetime_val;
+---------------------+----------------------+----------------------------+ | dt | format1 | format2 | +---------------------+----------------------+----------------------------+ | 1970-01-01 00:00:00 | 1/1/70 12:00:00 AM | January 1, 1970 00:00:00 | | 1987-03-05 12:30:15 | 3/5/87 12:30:15 PM | March 5, 1987 12:30:15 | | 1999-12-31 09:00:00 | 12/31/99 09:00:00 AM | December 31, 1999 09:00:00 | | 2000-06-04 15:45:30 | 6/4/00 03:45:30 PM | June 4, 2000 15:45:30 | +---------------------+----------------------+----------------------------+
TIME_FORMAT()
is
similar to DATE_FORMAT()
, but
it understands only time-related specifiers in the format string.
TIME_FORMAT()
works with
TIME
, DATETIME
, or TIMESTAMP
values.
mysql>SELECT dt,
->TIME_FORMAT(dt, '%r') AS '12-hour time',
->TIME_FORMAT(dt, '%T') AS '24-hour time'
->FROM datetime_val;
+---------------------+--------------+--------------+ | dt | 12-hour time | 24-hour time | +---------------------+--------------+--------------+ | 1970-01-01 00:00:00 | 12:00:00 AM | 00:00:00 | | 1987-03-05 12:30:15 | 12:30:15 PM | 12:30:15 | | 1999-12-31 09:00:00 | 09:00:00 AM | 09:00:00 | | 2000-06-04 15:45:30 | 03:45:30 PM | 15:45:30 | +---------------------+--------------+--------------+
If DATE_FORMAT()
or
TIME_FORMAT()
cannot produce
the results that you want, perhaps you can write a stored function
that does. Suppose that you want to convert 24-hour TIME
values to 12-hour format but with a
suffix of a.m.
or p.m.
rather than AM
or PM
.
The following function accomplishes that task. It uses TIME_FORMAT()
to do most of the work,
and then strips off the suffix supplied by %r
and replaces it with the desired
suffix:
CREATE FUNCTION time_ampm (t TIME) RETURNS VARCHAR(13) # mm:dd:ss {a.m.|p.m.} format BEGIN DECLARE ampm CHAR(4); IF TIME_TO_SEC(t) < 12*60*60 THEN SET ampm = 'a.m.'; ELSE SET ampm = 'p.m.'; END IF; RETURN CONCAT(LEFT(TIME_FORMAT(t, '%r'),9),ampm); END;
Use the function like this:
mysql>SELECT t1, time_ampm(t1) FROM time_val;
+----------+---------------+
| t1 | time_ampm(t1) |
+----------+---------------+
| 15:00:00 | 03:00:00 p.m. |
| 05:01:30 | 05:01:30 a.m. |
| 12:30:20 | 12:30:20 p.m. |
+----------+---------------+
For more information about writing stored functions, see Chapter 16.
18.188.135.58