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

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

  • Present starting in MariaDB Enterprise Server 10.6.4-1.

10.6 Community

  • Present starting in MariaDB Community Server 10.6.0.

10.5 Enterprise

  • Present starting in MariaDB Enterprise Server 10.5.3-1.

10.5 Community

  • Present starting in MariaDB Community Server 10.5.0.

10.4 Enterprise

  • Present starting in MariaDB Enterprise Server 10.4.6-1.

10.4 Community

  • Present starting in MariaDB Community Server 10.4.0.

10.3 Enterprise

  • Present starting in MariaDB Enterprise Server 10.3.16-1.

10.3 Community

  • Present starting in MariaDB Community Server 10.3.0.

10.2 Enterprise

  • Present starting in MariaDB Enterprise Server 10.2.25-1.

10.2 Community

  • Present starting in MariaDB Community Server 10.2.0.

EXTERNAL REFERENCES