DATE
Year, month, day
See also: Data Types in 10.6 ES, in 10.6 CS, in 10.5 ES, in 10.5 CS, in 10.4 ES, in 10.4 CS, in 10.3 ES, in 10.3 CS, in 10.2 ES, and in 10.2 CS
This page is part of MariaDB's Enterprise Documentation.
The parent of this page is: Data Types
Topics on this page:
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');
If SQL_MODE is strict (the default), the example above generates the following error and no values are inserted:
ERROR 1292 (22007): Incorrect date value: '10000,12,31' for column `doctest`.`date_range_example`.`example` at row 4
If SQL_MODE is not strict, the example above generates a warning and (possibly modified) values are inserted. The Below minimum range value is accepted because it contains a zero component. The Above maximum range value is truncated since it is an unacceptable date.
Warning (Code 1265): Data truncated for column 'example' at row 4
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-01-01 |
| Above maximum range | 0000-00-00 |
+---------------------+------------+
Date Expressions
When using a date value in an expression, such as DATE_ADD(), the following illustrates that a NULL is generated when a date value is not a real date and when a real date overflows:
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-01-01 | NULL |
| 0000-00-00 | NULL |
+------------+-----------------------------------+
Warning (Code 1441): Datetime function: datetime field overflow
Warning (Code 1441): Datetime function: datetime field overflow
Warning (Code 1292): Incorrect datetime value: '0000-00-00'
Invalid Dates
The following example enhances the SQL_MODE to ensure that ALLOW_INVALID_DATES is set and illustrates the difference between a day that is outside the range of 1 to 31 and one that is just too large for its month:
-- Disable STRICT_TRANS_TABLES and enable ALLOW_INVALID_DATES
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));
SET sql_mode=(SELECT CONCAT(@@sql_mode, ',ALLOW_INVALID_DATES'));
INSERT INTO date_range_example VALUES
('day is invalid for all months', '2019-12-32'),
('day is just large for February', '2019-02-31');
Warning (Code 1265): Data truncated for column 'example' at row 1
The resulting data would look like this:
SELECT * FROM date_range_example;
+--------------------------------+------------+
| description | example |
+--------------------------------+------------+
| day is invalid for all months | 0000-00-00 |
| day is just large for February | 2019-02-31 |
+--------------------------------+------------+
CHANGE HISTORY
Release Series | History |
|---|---|
10.6 Enterprise |
|
10.6 Community |
|
10.5 Enterprise |
|
10.5 Community |
|
10.4 Enterprise |
|
10.4 Community |
|
10.3 Enterprise |
|
10.3 Community |
|
10.2 Enterprise |
|
10.2 Community |
|
EXTERNAL REFERENCES
Additional information on this topic may be found in the MariaDB Public Knowledge Base.
Information specific to MariaDB SkySQL can be found on the DATE page in the SkySQL Documentation.