DATE
This page is part of MariaDB's Documentation.
The parent of this page is: Data Types for MariaDB Enterprise Server
Topics on this page:
Overview
Year, month, day.
EXAMPLES
DATE Format
CREATE TABLE date_formats_example (
description VARCHAR(30),
example DATE
);
INSERT INTO date_formats_example VALUES
('Full year', '2022-12-30'),
('Short year', '22-12-30'),
('Short year no delimiters', '221230'),
('No delimiters', '20221230'),
('Pipe delimiters', '22|2|3'),
('Forward slash delimiter', '22/12/30'),
('Backward slash delimiter', '22\12\30'),
('Asterisk delimiter', '22*12*30'),
('Comma delimiter', '22,2,3');
The resulting data would look like this:
SELECT * FROM date_formats_example;
+--------------------------+------------+
| description | example |
+--------------------------+------------+
| Full year | 2022-12-30 |
| Short year | 2022-12-30 |
| Short year no delimiters | 2022-12-30 |
| No delimiters | 2022-12-30 |
| Pipe delimiters | 2022-02-03 |
| Forward slash delimiter | 2022-12-30 |
| Backward slash delimiter | 2022-12-30 |
| Asterisk delimiter | 2022-12-30 |
| Comma delimiter | 2022-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 `test`.`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_
-- 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
EXTERNAL REFERENCES
Additional information on this topic may be found in the MariaDB Public Knowledge Base.