Related Documentation Download this Manual Excerpts from this Manual

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', and20150721as 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, seeSection 11.2, “Date and Time Data Types”.

Standard SQL and ODBC Date and Time Literals.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 produceDATE,TIME, andDATETIMEvalues, respectively, including a trailing fractional seconds part if specified. TheTIMESTAMPsyntax produces aDATETIMEvalue in MySQL becauseDATETIMEhas a range that more closely corresponds to the standard SQLTIMESTAMPtype, which has a year range from0001to9999. (The MySQLTIMESTAMPyear range is1970to2038.)

String and Numeric Literals in Date and Time Context.MySQL recognizesDATEvalues in these formats:

  • As a string in eitherYYYY-MM-DDorYY-MM-DDformat. Arelaxedsyntax 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 eitherYYYYMMDDorYYMMDDformat, 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 eitherYYYYMMDDorYYMMDDformat, provided that the number makes sense as a date. For example,19830905and830905are interpreted as”1983-09-05'.

MySQL recognizesDATETIMEandTIMESTAMPvalues in these formats:

  • As a string in eitherYYYY-MM-DD hh:mm:ssorYY-MM-DD hh:mm:ssformat. Arelaxedsyntax 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 byTrather 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 eitherYYYYMMDDhhmmssorYYMMDDhhmmssformat, 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 eitherYYYYMMDDhhmmssorYYMMDDhhmmssformat, provided that the number makes sense as a date. For example,19830905132800and830905132800are interpreted as”1983-09-05 13:28:00'.

ADATETIMEorTIMESTAMPvalue 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, seeSection 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 range70-99become1970-1999.

  • Year values in the range00-69become2000-2069.

See alsoSection 11.2.9, “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 than10.”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 than10.”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 inYYYYMMDDorYYYYMMDDhhmmssformat 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 inYYMMDDorYYMMDDhhmmssformat 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 thezerodate value. This occurs because the year and month values are99and03, 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 recognizesTIMEvalues in these formats:

  • As a string in”D hh:mm:ss'format. You can also use one of the followingrelaxedsyntaxes:”hh:mm:ss',”hh:mm',”D hh:mm',”D hh', or”ss'. HereDrepresents 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 inhhmmssformat, provided that it makes sense as a time. For example,101112is 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', andhhmmss.fractiontime formats, wherefractionis 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, seeSection 11.2.7, “Fractional Seconds in Time Values”.

ForTIMEvalues 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 than10.”8:3:2'is the same as”08:03:02'.