DATE

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.

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