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 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 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  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 ’ 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’ . 

