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.

URL: https://mariadb.com/kb/en/date-and-time-literals/