DATE

Overview

Year, month, day.

See also: Data Types in Xpand 6 and in 5.3

DETAILS

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.

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

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

CHANGE HISTORY

Release Series

History

6

  • Present starting in MariaDB Xpand 6.0.3.

5.3

  • Present starting in MariaDB Xpand 5.3.13.

EXTERNAL REFERENCES