DATE
This page is part of MariaDB's Enterprise Documentation.
The parent of this page is: Data Types for MariaDB Xpand
Topics on this page:
DETAILS
MySQL Compatibility
As with MySQL, Xpand will interpret input values supplied to DATE
and TIME
functions and convert them to a standard format. However, unpredictable results may occur if values are provided in other formats.
Xpand matches MySQL with the exception of:
Xpand does not support casting a
TIME
to aDATETIME
and returnsNULL
.Xpand does not support casting a
TIME
to aDATE
or aDATE
to aTIME.
When converting from a
DATETIME
to aDATE
, Xpand will discard the the time portion. MySQL will take fractional seconds into account and round the time part.Casting negative value to
DATETIME
will return a zero date. MySQL returnsNULL.
When the input to
LAST_DAY
is aDATETIME
, MySQL will round to the nearestDATE
before computingLAST_DAY
where Xpand does not. For example,LAST_DAY('2013-01-31 23:59:59.999999')
returns'2017-01-31'
on Xpand and'2017-02-01'
on MySQL.If an invalid format string is supplied to
STR_TO_DATE
, Xpand will returnNULL
. MySQL ignores extra characters at the end of format string.The results when using
EXTRACT
with compound units (e.g.,DAY_SECOND
,DAY_MICROSECOND
) may exclude some of the requested units or return incorrect results.Output from datetime functions (e.g.,
NOW()
,INTERVAL() FROM_UNIXTIME()
,SUBTIME()
) display microsecond precision by default.UNIX_TIMESTAMP()
andFROM_UNIXTIME
do not support input with fractional seconds.Boolean values (
True
,False
,Null
) are accepted in aDATETIME
column. This matches the behavior for MySQL v5.5. MySQL v5.6 provides an error message.On Xpand, specifying a zero value for day, month, or year within a date will result in a zero date ('0000-00-00'). MySQL will issue an error (if the sql_
mode for NO_ZERO_IN_DATE
) is set, or allow zero value for the specific portion of the date.
EXAMPLES
DATE Format
CREATE TABLE date_formats_example (
description VARCHAR(30),
example DATE
);
INSERT INTO date_formats_example VALUES
('Full year', '2019-12-30'),
('Short year', '19-12-30'),
('Short year no delimiters', '191230'),
('No delimiters', '20191230'),
('Pipe delimiters', '19|2|3'),
('Forward slash delimiter', '19/12/30'),
('Backward slash delimiter', '19\12\30'),
('Asterisk delimiter', '19*12*30'),
('Comma delimiter', '19,2,3');
The resulting data would look like this:
SELECT * FROM date_formats_example;
+--------------------------+------------+
| description | example |
+--------------------------+------------+
| Full year | 2019-12-30 |
| Short year | 2019-12-30 |
| Short year no delimiters | 2019-12-30 |
| No delimiters | 2019-12-30 |
| Pipe delimiters | 2019-02-03 |
| Forward slash delimiter | 2019-12-30 |
| Backward slash delimiter | 2019-12-30 |
| Asterisk delimiter | 2019-12-30 |
| Comma delimiter | 2019-02-03 |
+--------------------------+------------+
DATE Range
CREATE TABLE date_range_example (
description VARCHAR(30),
example DATE
);
INSERT INTO date_range_example VALUES
('Minimum date', '0001-01-01'),
('Maximum date', '9999-12-31'),
('Below minimum range', '0000*1*1'),
('Above maximum range', '10000,12,31');
The resulting data would look like this:
SELECT * FROM date_range_example;
+---------------------+------------+
| description | example |
+---------------------+------------+
| Minimum date | 0001-01-01 |
| Maximum date | 9999-12-31 |
| Below minimum range | 0000-00-00 |
| Above maximum range | 0000-00-00 |
+---------------------+------------+
Date Expressions
When using a date value in an expression, such as DATE_
SELECT example, DATE_ADD(example, INTERVAL 1 DAY)
FROM date_range_example;
+------------+-----------------------------------+
| example | DATE_ADD(example, INTERVAL 1 DAY) |
+------------+-----------------------------------+
| 0001-01-01 | 0001-01-02 |
| 9999-12-31 | NULL |
| 0000-00-00 | NULL |
| 0000-00-00 | NULL |
+------------+-----------------------------------+
CHANGE HISTORY
Release Series | History |
---|---|
6 |
|
5.3 |
|
EXTERNAL REFERENCES
Information specific to MariaDB SkySQL can be found on the DATE page in the SkySQL Documentation.