Rechercher dans le manuel MySQL
9.1.3 Date and Time Literals
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, consult these sections:
Standard SQL and ODBC Date and Time Literals. Standard SQL permits temporal literals to be specified using a type keyword and a string. The space between the keyword and string is optional.
MySQL recognizes those constructions and also the corresponding ODBC syntax:
{ d 'str' }
{ t 'str' }
{ ts 'str' }
MySQL uses the type keyword and these constructions 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
.)
String and Numeric Literals in Date and Time Context.
MySQL recognizes DATE
values in
these formats:
As a string in either
'YYYY-MM-DD'
or'YY-MM-DD'
format. A “relaxed” syntax is permitted: Any punctuation character may be used as the delimiter between date parts. For example,'2012-12-31'
,'2012/12/31'
,'2012^12^31'
, and'2012@12@31'
are equivalent.As a string with no delimiters in either
'YYYYMMDD'
or'YYMMDD'
format, provided that the string makes sense as a date. For example,'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
orYYMMDD
format, provided that the number makes sense as a date. For example,19830905
and830905
are interpreted as'1983-09-05'
.
MySQL recognizes DATETIME
and
TIMESTAMP
values in these
formats:
As a string in either
'YYYY-MM-DD hh:mm:ss'
or'YY-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,'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
'YYYYMMDDhhmmss'
or'YYMMDDhhmmss'
format, provided that the string makes sense as a date. For example,'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
orYYMMDDhhmmss
format, provided that the number makes sense as a date. For example,19830905132800
and830905132800
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.3.5, “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
are converted to1970-1999
.Year values in the range
00-69
are converted to2000-2069
.
See also Section 11.3.7, “Two-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'
. HereD
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
, orhhmmss
.
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.3.5, “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'
.
Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-date-and-time-literals.html/
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.