Datetime values

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 as 20150721123456.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 returns NULL because 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_TABLES or NO_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 T or 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 returns NULL because it contains a meaningless minute part.

• YYYYMMDDhhmmss or YYMMDDhhmmss, 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:

• YYMMDD for a 6-digit value

• YYYYMMDD for an 8-digit value

• YYMMDDHHMMSS for a 12-digit value

• YYYYMMDDHHMMSS for 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'. D represents 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, and hhmmss, 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.