Bug #103781 PREPARE DATE_ADD/DATE_SUB incorrectly returns DATETIME
Submitted: 22 May 20:49 Modified: 23 May 1:54
Reporter: Max Nyman Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.23/8.0.25 OS:Ubuntu
Assigned to: CPU Architecture:Any

[22 May 20:49] Max Nyman
Description:
When using prepared statements, the DATE_ADD and DATE_SUB functions return value is in DateTime format, even if calculations involve only YEAR, MONTH, and DAY parts (that is, no time parts).

This behavior is changed from at least MySQL version 8.0.21, in which the statement only returned the date part.

PREPARE stmt1 FROM "SELECT DATE_ADD(?, INTERVAL 1 DAY) AS a";
SET @pc = '2021-05-22';
EXECUTE stmt1 USING @pc;

Returns # a -> '2021-05-23 00:00:00.000000'

As a note, the below statement yields another result:

PREPARE stmt2 FROM "SELECT DATE_ADD('2021-05-22', INTERVAL 1 DAY) AS a";
EXECUTE stmt2;

Returns # a -> '2021-05-23'

How to repeat:
PREPARE stmt1 FROM "SELECT DATE_ADD(?, INTERVAL 1 DAY)";

SET @pc = '2021-05-22';

EXECUTE stmt1 USING @pc;

Suggested fix:
Returning only the date part when calculations do not involve any time parts.
[23 May 1:54] MySQL Verification Team
Thank you for the bug report.
[3 Jun 21:05] Roy Lyseng
The behavior has been changed because we now do type resolving based on the prepared statement only. For DATE_ADD, if the first argument is a dynamic parameter, its type is assumed to be DATETIME, since it is the most comprehensive temporal type, and since a DATE value can easily be converted to a DATETIME value.

However, you can tell MySQL that the first argument is a DATE value, and hence the returned type is a DATE value, with the syntax:

  PREPARE stmt1 FROM "SELECT DATE_ADD(CAST(? AS DATE), INTERVAL 1 DAY)";