Database Reference
In-Depth Information
ISO format, although common, is not used by all database systems, which can cause
problems if you 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 how dates should
look and how MySQL actually represents them.
A question frequently asked by newcomers 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. Instead, ask,
“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 with implications both
for data entry (input) and for displaying query results (output):
• 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 them, you can store them as
strings (for example, in a
CHAR
column). But then you can't operate on them as dates.
Chapter 11
covers the topic of date rewriting for data entry, and
Chapter 12
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 storing them into a
DATE
column.
• For display purposes, you can rewrite dates to non-ISO formats. The
DATE_FOR
MAT()
function provides a lot of flexibility for changing date values into other for‐
mats (see later in this section). You can also use functions such as
YEAR()
to extract
parts of dates for display (see
Recipe 6.8
). For additional discussion, see
Recipe 12.14
.
One way to rewrite non-ISO values for date entry is to use the
STR_TO_DATE()
function,
which takes a string representing a temporal value and a format string that specifies the
“syntax” of the value. Within the formatting string, 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, 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. To
display dates or times in other formats, use the
DATE_FORMAT()
or
TIME_FORMAT()
function to rewrite them. If you require a more specialized format those functions can‐
not provide, write a stored function.
