Date and Time Literals
Contents
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.