Date and Time Literals

Standard syntaxes

MariaDB supports the SQL standard and ODBC syntaxes for DATE, TIME and TIMESTAMP literals.

SQL standard syntax:

• DATE 'string'
• TIME 'string'
• TIMESTAMP 'string'

ODBC syntax:

• {d 'string'}
• {t 'string'}
• {ts 'string'}

The timestamp literals are treated as DATETIME literals, because in MariaDB the range of DATETIME is closer to the TIMESTAMP range in the SQL standard.

string is a string in a proper format, as explained below.

DATE literals

A DATE string is a string in one of the following formats: 'YYYY-MM-DD' or 'YY-MM-DD'. Note that any punctuation character can be used as delimiter. All delimiters must consist of 1 character. Different delimiters can be used in the same string. Delimiters are optional (but if one delimiter is used, all delimiters must be used).

A DATE literal can also be an integer, in one of the following formats: YYYYMMDD or YYMMDD.

All the following DATE literals are valid, and they all represent the same value:

'19940101'
'940101'
'1994-01-01'
'94/01/01'
'1994-01/01'
'94:01!01'
19940101
940101

DATETIME literals

A DATETIME string is a string in one of the following formats: 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS'. Note that any punctuation character can be used as delimiter for the date part and for the time part. All delimiters must consist of 1 character. Different delimiters can be used in the same string. The hours, minutes and seconds parts can consist of one character. For this reason, delimiters are mandatory for DATETIME literals.

The delimiter between the date part and the time part can be a T or any sequence of space characters (including tabs, new lines and carriage returns).

A DATETIME literal can also be a number, in one of the following formats: YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD or YYMMDD. In this case, all the time subparts must consist of 2 digits.

All the following DATE literals are valid, and they all represent the same value:

'1994-01-01T12:30:03'
'1994/01/01\n\t 12+30+03'
'1994/01\\01\n\t 12+30-03'
'1994-01-01 12:30:3'

TIME literals

A TIME string is a string in one of the following formats: 'D HH:MM:SS', 'HH:MM:SS, 'D HH:MM', 'HH:MM', 'D HH', or 'SS'. D is a value from 0 to 34 which represents days. : is the only allowed delimiter for TIME literals. Delimiters are mandatory, with an exception: the 'HHMMSS' format is allowed. When delimiters are used, each part of the literal can consist of one character.

A TIME literal can also be a number in one of the following formats: HHMMSS, MMSS, or SS.

The following literals are equivalent:

'09:05:00'
'9:05:0'
'9:5:0'
'090500'

2-digit years

The year part in DATE and DATETIME literals is determined as follows:

• 70 - 99 = 1970 - 1999
• 00 - 69 = 2000 - 2069

Microseconds

DATETIME and TIME literals can have an optional microseconds part. For both string and numeric forms, it is expressed as a decimal part. Up to 6 decimal digits are allowed. Examples:

'12:30:00.123456'
123000.123456

See Microseconds in MariaDB for details.

Date and time literals and the SQL_MODE

Unless the SQL_MODE NO_ZERO_DATE flag is set, some special values are allowed: the '0000-00-00' DATE, the '00:00:00' TIME, and the 0000-00-00 00:00:00 DATETIME.

If the ALLOW_INVALID_DATES flag is set, the invalid dates (for example, 30th February) are allowed. If not, if the NO_ZERO_DATE is set, an error is produced; otherwise, a zero-date is returned.

Unless the NO_ZERO_IN_DATE flag is set, each subpart of a date or time value (years, hours...) can be set to 0.

See also

• Date and time units