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');

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_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