11.3. Date and Time Types
The date and time types for representing temporal values are
DATETIME
, DATE
,
TIMESTAMP
, TIME
, and
YEAR
. Each temporal type has a range of legal
values, as well as a “zero” value that may be used
when you specify an illegal value that MySQL cannot represent. The
TIMESTAMP
type has special automatic updating
behavior, described later on. For temporary type storage
requirements, see Section 11.5, “Data Type Storage Requirements”.
MySQL gives warnings or errors if you try to insert an illegal
date. By setting the SQL mode to the appropriate value, you can
specify more exactly what kind of dates you want MySQL to support.
(See Section 5.2.5, “The Server SQL Mode”.) You can get MySQL to
accept certain dates, such as '1999-11-31'
, by
using the ALLOW_INVALID_DATES
SQL mode. This is
useful when you want to store a “possibly wrong”
value which the user has specified (for example, in a web form) in
the database for future processing. Under this mode, MySQL
verifies only that the month is in the range from 0 to 12 and that
the day is in the range from 0 to 31. These ranges are defined to
include zero because MySQL allows you to store dates where the day
or month and day are zero in a DATE
or
DATETIME
column. This is extremely useful for
applications that need to store a birthdate for which you do not
know the exact date. In this case, you simply store the date as
'1999-00-00'
or
'1999-01-00'
. If you store dates such as these,
you should not expect to get correct results for functions such as
DATE_SUB()
or DATE_ADD
that
require complete dates. (If you do not want
to allow zero in dates, you can use the
NO_ZERO_IN_DATE
SQL mode).
MySQL also allows you to store '0000-00-00'
as
a “dummy date” (if you are not using the
NO_ZERO_DATE
SQL mode). This is in some cases
is more convenient (and uses less space in data and index) than
using NULL
values.
Here are some general considerations to keep in mind when working
with date and time types:
MySQL retrieves values for a given date or time type in a
standard output format, but it attempts to interpret a variety
of formats for input values that you supply (for example, when
you specify a value to be assigned to or compared to a date or
time type). Only the formats described in the following
sections are supported. It is expected that you supply legal
values. Unpredictable results may occur if you use values in
other formats.
-
Dates containing two-digit year values are ambiguous because
the century is unknown. MySQL interprets two-digit year values
using the following rules:
Although MySQL tries to interpret values in several formats,
dates always must be given in year-month-day order (for
example, '98-09-04'
), rather than in the
month-day-year or day-month-year orders commonly used
elsewhere (for example, '09-04-98'
,
'04-09-98'
).
MySQL automatically converts a date or time type value to a
number if the value is used in a numeric context and vice
versa.
-
By default, when MySQL encounters a value for a date or time
type that is out of range or otherwise illegal for the type
(as described at the beginning of this section), it converts
the value to the “zero” value for that type. The
exception is that out-of-range TIME
values
are clipped to the appropriate endpoint of the
TIME
range.
The following table shows the format of the
“zero” value for each type. Note that the use of
these values produces warnings if the
NO_ZERO_DATE
SQL mode is enabled.
The “zero” values are special, but you can store
or refer to them explicitly using the values shown in the
table. You can also do this using the values
'0'
or 0
, which are
easier to write.
“Zero” date or time values used through MyODBC
are converted automatically to NULL
in
MyODBC 2.50.12 and above, because ODBC cannot handle such
values.