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.

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