DATE

USAGE

DATE

DETAILS

Data Type

Minimum Value

Maximum Value

DATE

0001-01-01

9999-12-31

MySQL Compatibility

As with MySQL, Xpand will interpret input values supplied to DATE and TIME functions and convert them to a standard format. However, unpredictable results may occur if values are provided in other formats.

Xpand matches MySQL with the exception of:

  • Xpand does not support casting a TIME to a DATETIME and returns NULL.

  • Xpand does not support casting a TIME to a DATE or a DATE to a TIME.

  • When converting from a DATETIME to a DATE, Xpand will discard the the time portion. MySQL will take fractional seconds into account and round the time part.

  • Casting negative value to DATETIME will return a zero date. MySQL returns NULL.

  • When the input to LAST_DAY is a DATETIME, MySQL will round to the nearest DATE before computing LAST_DAY where Xpand does not. For example, LAST_DAY('2013-01-31 23:59:59.999999') returns '2017-01-31' on Xpand and '2017-02-01' on MySQL.

  • If an invalid format string is supplied to STR_TO_DATE, Xpand will return NULL. MySQL ignores extra characters at the end of format string.

  • The results when using EXTRACT with compound units (e.g., DAY_SECOND, DAY_MICROSECOND) may exclude some of the requested units or return incorrect results.

  • Output from datetime functions (e.g., NOW(), INTERVAL() FROM_UNIXTIME(), SUBTIME()) display microsecond precision by default.

  • UNIX_TIMESTAMP() and FROM_UNIXTIME do not support input with fractional seconds.

  • Boolean values (True, False, Null) are accepted in a DATETIME column. This matches the behavior for MySQL v5.5. MySQL v5.6 provides an error message.

  • On Xpand, specifying a zero value for day, month, or year within a date will result in a zero date ('0000-00-00'). MySQL will issue an error (if the sql_mode for NO_ZERO_IN_DATE) is set, or allow zero value for the specific portion of the date.

SYNONYMS

SCHEMA

PARAMETERS

SKYSQL

PRIVILEGES

EXAMPLES

Formats

Xpand supports the following date formats:

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

When a DATE value is selected, the DATE_FORMAT() function can be used to control the output format. By default, the value is returned in the YYYY-MM-DD format, which is equivalent to DATE_FORMAT(<date>, '%Y-%m-%d'):

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

Range

Xpand does not reject out-of-range values for the DATE data type. Out-of-range DATE values are always accepted and replaced with "zero dates", even if sql_mode=STRICT_TRANS_TABLES is set:

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');
SELECT * FROM date_range_example;
+---------------------+------------+
| description         | example    |
+---------------------+------------+
| Minimum date        | 0001-01-01 |
| Maximum date        | 9999-12-31 |
| Below minimum range | 0000-00-00 |
| Above maximum range | 0000-00-00 |
+---------------------+------------+

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-00-00 | NULL                              |
| 0000-00-00 | NULL                              |
+------------+-----------------------------------+

Type Casting

Xpand does not support casting a DATE to a TIME and a TIME to a DATE, and returns 00:00:00 and NULL respectively:

SELECT CAST(DATE(NOW()) AS TIME);
+---------------------------+
| CAST(DATE(NOW()) AS TIME) |
+---------------------------+
| 00:00:00                  |
+---------------------------+
SELECT CONVERT(TIME(NOW()), DATE);
+----------------------------+
| CONVERT(TIME(NOW()), DATE) |
+----------------------------+
| NULL                       |
+----------------------------+

When converting from a DATETIME to a DATE, time portion is discarded:

SELECT CAST(NOW() AS DATE);
+---------------------+
| CAST(NOW() AS DATE) |
+---------------------+
| 2022-10-23          |
+---------------------+

Zero Date Literals

Specifying a zero value for the day, month, or year within a date will result in a zero date (0000-00-00):

SELECT DATE('0000-01-01'), DATE('2022-00-30'), DATE('2022-01-00');
+--------------------+--------------------+--------------------+
| DATE('0000-01-01') | DATE('2022-00-30') | DATE('2022-01-00') |
+--------------------+--------------------+--------------------+
| 0000-00-00         | 0000-00-00         | 0000-00-00         |
+--------------------+--------------------+--------------------+

ERROR HANDLING

FEATURE INTERACTION

RESPONSES

DIAGNOSIS

ISO 9075:2016

CHANGE HISTORY

Release Series

History

23.09

  • Present starting in MariaDB Xpand 23.09.1.

6.1

  • Present starting in MariaDB Xpand 6.1.0.

6.0

  • Present starting in MariaDB Xpand 6.0.3.

5.3

  • Present starting in MariaDB Xpand 5.3.13.

Release Series

History

6.0

  • Present starting in MariaDB Xpand 6.0.3.

5.3

  • Present starting in MariaDB Xpand 5.3.13.

Release Series

History

6.1

  • Present starting in MariaDB Xpand 6.1.0.

EXTERNAL REFERENCES