MySQL 5.7 Reference Manual Including MySQL NDB Cluster 7.5 and NDB Cluster 7.6
Date and time values can be represented in several formats, such
as quoted strings or as numbers, depending on the exact type of
the value and other factors. For example, in contexts where
MySQL expects a date, it interprets any of
'2015-07-21', '20150721',
and 20150721 as a date.
This section describes the acceptable formats for date and time literals. For more information about the temporal data types, such as the range of permitted values, see Section 11.2, “Date and Time Data Types”.
Standard SQL requires temporal literals to be specified using a type keyword and a string. The space between the keyword and string is optional.
DATE 'str' TIME 'str' TIMESTAMP 'str'
MySQL recognizes but, unlike standard SQL, does not require the type keyword. Applications that are to be standard-compliant should include the type keyword for temporal literals.
MySQL also recognizes the ODBC syntax corresponding to the standard SQL syntax:
{ d 'str' }
{ t 'str' }
{ ts 'str' }
MySQL uses the type keywords and the ODBC constructions to
produce DATE,
TIME, and
DATETIME values, respectively,
including a trailing fractional seconds part if specified. The
TIMESTAMP syntax produces a
DATETIME value in MySQL because
DATETIME has a range that more
closely corresponds to the standard SQL
TIMESTAMP type, which has a
year range from 0001 to
9999. (The MySQL
TIMESTAMP year range is
1970 to 2038.)
MySQL recognizes DATE values in
these formats:
As a string in either
'
or
YYYY-MM-DD''
format. A “relaxed” syntax is permitted: Any
punctuation character may be used as the delimiter between
date parts. For example, YY-MM-DD''2012-12-31',
'2012/12/31',
'2012^12^31', and
'2012@12@31' are equivalent.
As a string with no delimiters in either
'
or YYYYMMDD''
format, provided that the string makes sense as a date.
For example, YYMMDD''20070523' and
'070523' are interpreted as
'2007-05-23', but
'071332' is illegal (it has nonsensical
month and day parts) and becomes
'0000-00-00'.
As a number in either YYYYMMDD
or YYMMDD format, provided that
the number makes sense as a date. For example,
19830905 and 830905
are interpreted as '1983-09-05'.
MySQL recognizes DATETIME and
TIMESTAMP values in these
formats:
As a string in either ' or
YYYY-MM-DD
hh:mm:ss'' format. A
“relaxed” syntax is permitted here, too: Any
punctuation character may be used as the delimiter between
date parts or time parts. For example,
YY-MM-DD
hh:mm:ss''2012-12-31 11:30:45',
'2012^12^31 11+30+45',
'2012/12/31 11*30*45', and
'2012@12@31 11^30^45' are equivalent.
The only delimiter recognized between a date and time part and a fractional seconds part is the decimal point.
The date and time parts can be separated by
T rather than a space. For example,
'2012-12-31 11:30:45'
'2012-12-31T11:30:45' are equivalent.
As a string with no delimiters in either
'
or
YYYYMMDDhhmmss''
format, provided that the string makes sense as a date.
For example, YYMMDDhhmmss''20070523091528' and
'070523091528' are interpreted as
'2007-05-23 09:15:28', but
'071122129015' is illegal (it has a
nonsensical minute part) and becomes '0000-00-00
00:00:00'.
As a number in either
YYYYMMDDhhmmss or
YYMMDDhhmmss format, provided
that the number makes sense as a date. For example,
19830905132800 and
830905132800 are interpreted as
'1983-09-05 13:28:00'.
A DATETIME or
TIMESTAMP value can include a
trailing fractional seconds part in up to microseconds (6
digits) precision. The fractional part should always be
separated from the rest of the time by a decimal point; no
other fractional seconds delimiter is recognized. For
information about fractional seconds support in MySQL, see
Section 11.2.7, “Fractional Seconds in Time Values”.
Dates containing two-digit year values are ambiguous because the century is unknown. MySQL interprets two-digit year values using these rules:
Year values in the range 70-99 become
1970-1999.
Year values in the range 00-69 become
2000-2069.
See also Section 11.2.10, “2-Digit Years in Dates”.
For values specified as strings that include date part
delimiters, it is unnecessary to specify two digits for month
or day values that are less than 10.
'2015-6-9' is the same as
'2015-06-09'. Similarly, for values
specified as strings that include time part delimiters, it is
unnecessary to specify two digits for hour, minute, or second
values that are less than 10.
'2015-10-30 1:2:3' is the same as
'2015-10-30 01:02:03'.
Values specified as numbers should be 6, 8, 12, or 14 digits
long. If a number is 8 or 14 digits long, it is assumed to be
in YYYYMMDD or
YYYYMMDDhhmmss format and that the
year is given by the first 4 digits. If the number is 6 or 12
digits long, it is assumed to be in
YYMMDD or
YYMMDDhhmmss format and that the
year is given by the first 2 digits. Numbers that are not one
of these lengths are interpreted as though padded with leading
zeros to the closest length.
Values specified as nondelimited strings are interpreted
according their length. For a string 8 or 14 characters long,
the year is assumed to be given by the first 4 characters.
Otherwise, the year is assumed to be given by the first 2
characters. The string is interpreted from left to right to
find year, month, day, hour, minute, and second values, for as
many parts as are present in the string. This means you should
not use strings that have fewer than 6 characters. For
example, if you specify '9903', thinking
that represents March, 1999, MySQL converts it to the
“zero” date value. This occurs because the year
and month values are 99 and
03, but the day part is completely missing.
However, you can explicitly specify a value of zero to
represent missing month or day parts. For example, to insert
the value '1999-03-00', use
'990300'.
MySQL recognizes TIME values in
these formats:
As a string in 'D hh:mm:ss'
format. You can also use one of the following
“relaxed” syntaxes:
'hh:mm:ss',
'hh:mm', 'D
hh:mm', 'D hh',
or 'ss'. Here
D represents days and can have
a value from 0 to 34.
As a string with no delimiters in
'hhmmss' format, provided that
it makes sense as a time. For example,
'101112' is understood as
'10:11:12', but
'109712' is illegal (it has a
nonsensical minute part) and becomes
'00:00:00'.
As a number in hhmmss format,
provided that it makes sense as a time. For example,
101112 is understood as
'10:11:12'. The following alternative
formats are also understood:
ss,
mmss, or
hhmmss.
A trailing fractional seconds part is recognized in the
'D hh:mm:ss.fraction',
'hh:mm:ss.fraction',
'hhmmss.fraction', and
hhmmss.fraction time formats, where
fraction is the fractional part in up to
microseconds (6 digits) precision. The fractional part should
always be separated from the rest of the time by a decimal
point; no other fractional seconds delimiter is recognized.
For information about fractional seconds support in MySQL, see
Section 11.2.7, “Fractional Seconds in Time Values”.
For TIME values specified as
strings that include a time part delimiter, it is unnecessary
to specify two digits for hours, minutes, or seconds values
that are less than 10.
'8:3:2' is the same as
'08:03:02'.