OceanBase Database supports datetime literals in string and number formats.
Differences between strings and numbers:
A string can contain all delimiters, such as
'2015-07-21 12:34:56.789', or no delimiters, such as'20150721'.A number can use only a decimal point (
.) as the delimiter between seconds and microseconds, such as20150721123456.789.
When you need to use delimiters in other scenarios, we recommend that you use only dashes (-), forward slashes (/), and colons (:).
DATE values
OceanBase Database recognizes a DATE value in one of the following formats:
'YYYY-MM-DD'or'YY-MM-DD'. Any punctuation characters can be used as delimiters between date parts. For example, '2012-12-31', '2012/12/31', '2012^12^31', and '2012@12@31' are equivalent.'YYYYMMDD'or'YYMMDD', provided that the string is a meaningful date value. For example, '20070523' or '070523' can be parsed as '2007-05-23', but '071532' is invalid and returnsNULLbecause it contains meaningless month and day parts.'YYYYMMDD'or'YYMMDD', provided that the number is a meaningful date value. For example, 19830905 or 830905 can be parsed as "1983-09-05".Notice
If sql_mode contains
STRICT_TRANS_TABLESorNO_ZERO_IN_DATE, and the month or day part of a date contains 0, an error is returned, except for '0000-00-00'.
DATETIME and TIMESTAMP values
OceanBase Database recognizes a DATETIME or TIMESTAMP value in the following formats:
'YYYY-MM-DD hh:mm:ss'or'YY-MM-DD hh:mm:ss'. Any punctuation characters can be used as delimiters between date 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.Note that the decimal point is the only delimiter that is recognizable between a date and time part and a fractional part of seconds. The date and time parts can be separated either by a letter
Tor a space. For example, '2012-12-31 11:30:45' and '2012-12-31T11:30:45' are equivalent.'YYYYMMDDhhmmss'or'YYMMDDhhmmss', provided that the string is a meaningful date value. For example, '20070523091528' or '070523091528' can be parsed as '2007-05-23 09:15:28', but '071122129015' is invalid and returnsNULLbecause it contains a meaningless minute part.YYYYMMDDhhmmssorYYMMDDhhmmss, provided that the string is a meaningful date value. For example, 19830905132800 and 830905132800 can be parsed as '1983-09-05 13:28:00'.
A DATETIME or TIMESTAMP value can include a trailing fractional seconds part of at most 6 digits to achieve a precision of up to microseconds. The fractional part can be separated from other time parts only with a decimal point. Other fractional part delimiters are unrecognizable.
OceanBase Database uses the following rules to parse a date that contains a two-digit year value:
A year value in the range of 70-99 becomes 1970-1999.
A year value in the range of 00-69 becomes 2000-2069.
You do not need to specify two digits for a month or day value that is less than 10. For example, '2015-6-9' is equivalent to '2015-06-09'. Likewise, you do not need to specify two digits for an hour, minute, or second value that is smaller than 10. For example, '2015-10-30 1:2:3' is equivalent to '2015-10-30 01:02:03'.
In addition, OceanBase Database selects a format based on the value length:
YYMMDDfor a 6-digit valueYYYYMMDDfor an 8-digit valueYYMMDDHHMMSSfor a 12-digit valueYYYYMMDDHHMMSSfor a 14-digit value
TIME values
OceanBase Database recognizes a TIME value in one of the following formats:
'D hh:mm:ss', or any of the following:'hh:mm:ss','hh:mm','D hh:mm','D hh', and'ss'.Drepresents the number of days, and the valid values are an integer from 0 to 34.'hhmmss'without delimiters, provided that the number is a meaningful date value. For example, '101112' can be parsed as '10:11:12', but '109712' is invalid and becomes '00:00:00' because it contains a meaningless minute part.hhmmss, or any of the following:ss,mmss, andhhmmss, provided that the number is a meaningful date value. For example, 101112 can be parsed as '10:11:12'.
A trailing fractional seconds part is recognized in the following formats: 'D hh:mm:ss.fraction', 'hh:mm:ss.fraction', 'hhmmss.fraction', and hhmmss.fraction. The fractional part can contain at most 6 digits to achieve a precision of up to microseconds. The fractional part can be separated from other time parts only with a decimal point. Other fractional part delimiters are unrecognizable.