![background image](http://html1.mh-extra.com/html/oracle/5-0/5-0_reference-manual_1646196867.webp)
Date and Time Types
847
Conversion of
DATETIME
and
TIMESTAMP
values:
• Conversion to a
DATE
value discards the time part because the
DATE
type contains no time
information.
• Conversion to a
TIME
value discards the date part because the
TIME
type contains no date
information.
Conversion of
TIME
values:
MySQL converts a time value to a date or date-and-time value by parsing the string value of the time
as a date or date-and-time. This is unlikely to be useful. For example,
'23:12:31'
interpreted as a
date becomes
'2032-12-31'
. Time values not valid as dates become
'0000-00-00'
or
NULL
.
Prior to MySQL 5.0.42, when
DATE
values are compared with
DATETIME
values, the time portion of
the
DATETIME
value is ignored, or the comparison could be performed as a string compare. Starting
from MySQL 5.0.42, a
DATE
value is coerced to the
DATETIME
type by adding the time portion as
'00:00:00'
. To mimic the old behavior, use the
CAST()
[948]
function to cause the comparison
operands to be treated as previously. For example:
date_col
= CAST(
datetime_col
AS DATE)
As of MySQL 5.0.8, conversion of
TIME
or
DATETIME
values to numeric form (for example, by adding
+0
) results in a double-precision value with a microseconds part of
.000000
:
mysql>
SELECT CURTIME(), CURTIME()+0;
+-----------+---------------+
| CURTIME() | CURTIME()+0 |
+-----------+---------------+
| 10:41:36 | 104136.000000 |
+-----------+---------------+
mysql>
SELECT NOW(), NOW()+0;
+---------------------+-----------------------+
| NOW() | NOW()+0 |
+---------------------+-----------------------+
| 2007-11-30 10:41:47 | 20071130104147.000000 |
+---------------------+-----------------------+
Before MySQL 5.0.8, the conversion results in an integer value with no microseconds part.
11.1.5.8. Two-Digit Years in Dates
Date values with two-digit years are ambiguous because the century is unknown. Such values must be
interpreted into four-digit form because MySQL stores years internally using four digits.
For
DATETIME
,
DATE
, and
TIMESTAMP
types, MySQL interprets dates specified with ambiguous year
values using these rules:
• Year values in the range
00-69
are converted to
2000-2069
.
• Year values in the range
70-99
are converted to
1970-1999
.
For
YEAR
, the rules are the same, with this exception: A numeric
00
inserted into
YEAR(4)
results in
0000
rather than
2000
. To specify zero for
YEAR(4)
and have it be interpreted as
2000
, specify it as a
string
'0'
or
'00'
.
Remember that these rules are only heuristics that provide reasonable guesses as to what your data
values mean. If the rules used by MySQL do not produce the values you require, you must provide
unambiguous input containing four-digit year values.
ORDER BY
properly sorts
YEAR
values that have two-digit years.
Some functions like
MIN()
[971]
and
MAX()
[971]
convert a
YEAR
to a number. This means that a
value with a two-digit year does not work properly with these functions. The fix in this case is to convert
the
YEAR
to four-digit year format.
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...