DATE_FORMAT() in MySQL return unexpected string in side stored procedure

Question

I am facing an issue with date_format() in a stored procedure. When I am trying to format a datetime to string from outside the stored procedure, it works perfectly - as shown here:

SELECT DATE_FORMAT('2021-04-07 23:59:59.999999', "%d %b %Y");

Output is 07 Apr 2021, which expected.

But inside the stored procedure, it returns 08 Apr 2021. I think the issue is with .999999 part. If I remove that then date_format() return 07 Apr 2021. How can I solve it?

DELIMITER $$
CREATE PROCEDURE `App_Date_Format`(IN  pDateTime DATETIME)
BEGIN
    SELECT DATE_FORMAT(pDateTime, "%d %b %Y");
END$$
DELIMITER ;

NB: this is a part of my full stored procedure

Answer 1

You define the parameter as DATETIME, so provided value is rounded. Define it as DATETIME(6):

CREATE PROCEDURE `App_Date_Format`(IN  pDateTime DATETIME(6))
BEGIN
    SELECT DATE_FORMAT(pDateTime, "%d %b %Y");
END

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=d1017437fa575d42098b6f18014d9f0f