DATE

Syntax

DATE

Description

A date. The supported range is '1000-01-01' to '9999-12-31'. MariaDB displays DATE values in 'YYYY-MM-DD' format, but can be assigned dates in looser formats, including strings or numbers, as long as they make sense. These include a short year, YY-MM-DD, no delimiters, YYMMDD, or any other acceptable delimiter, for example YYYY/MM/DD. For details, see date and time literals.

'0000-00-00' is a permitted special value (zero-date), unless the NO_ZERO_DATE SQL_MODE is used. Also, individual components of a date can be set to 0 (for example: '2015-00-12'), unless the NO_ZERO_IN_DATE SQL_MODE is used. In many cases, the result of en expression involving a zero-date, or a date with zero-parts, is NULL. If the ALLOW_INVALID_DATES SQL_MODE is enabled, if the day part is in the range between 1 and 31, the date does not produce any error, even for months that have less than 31 days.

See also: Data Types for MariaDB Enterprise Server 11.4, in 10.6 ES, in 10.5 ES, in 10.4 ES, in 10.3 ES, in 10.2 ES, in 10.6 CS, in 10.5 CS, in 10.4 CS, in 10.3 CS, and in 10.2 CS.

Oracle Mode

In Oracle mode, DATE with a time portion is a synonym for DATETIME. See also mariadb_schema.

Examples

CREATE TABLE t1 (d DATE);

INSERT INTO t1 VALUES ("2010-01-12"), ("2011-2-28"), ('120314'),('13*04*21');

SELECT * FROM t1;
+------------+
| d          |
+------------+
| 2010-01-12 |
| 2011-02-28 |
| 2012-03-14 |
| 2013-04-21 |
+------------+

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_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'));
<</code>>

<<sql>>
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 |
+--------------------------------+------------+

See Also

• mariadb_schema data type qualifier