Creating a mysql function to manipulate a date

Question

I want to create a function in mysql 5.7 which receives two dates and checks if there is a difference. If yes it should add one day to the second date and return it. If not, it should return the date without any manipulation.

Unfortunately I receive a syntax error every time I want to save the function (using TablePlus with full rights).

My function:

CREATE FUNCTION `CheckCreatedAt`(timeStart DATETIME, timeEnd DATETIME) RETURNS datetime
BEGIN 
    DECLARE newDate DATETIME;

    IF TIMESTAMPDIFF(DAY, timeStart, timeEnd) != 0 
    THEN 
        SET newDate = DATE_ADD(timeEnd, INTERVAL 1 DAY);
   ELSE 
        SET newDate = timeEnd;
    END IF;

    RETURN newDate;
END

The error-message:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ELSE SET newDate = timeEnd; END IF;

RETURN newDate; END' at line 6

I don't see any evidence that you are setting delimiters.https://dev.mysql.com/doc/refman/8.0/en/stored-programs-defining.html — P.Salmon, Nov 15 '19 at 09:53
Looks like that the problem is an invisible character at the start of the ELSE-line. Remove that and you should be ok. — slaakso, Nov 15 '19 at 09:55
I concur with @slaakso copy/paste the code into a decent editor. You have a corruption which show up as `37` a `?` after the `INVERVAL 1 DAY);` and before the `ELSE` — RiggsFolly, Nov 15 '19 at 09:59

score 0 · Answer 1 · answered Nov 15 '19 at 09:55

When creating stored procedurs and functions, you need to change the delimiter in order that you can notify MySQL where your code ends.

This is achieved using the DELIMITER command. For example:

DELIMITER $$

Will change the delimiter to a double dollar.

Modify your code to the following and it will create your function (barring other syntax errors):

DELIMITER $$
CREATE FUNCTION `CheckCreatedAt`(timeStart DATETIME, timeEnd DATETIME) RETURNS datetime
BEGIN 
    DECLARE newDate DATETIME;

    IF TIMESTAMPDIFF(DAY, timeStart, timeEnd) != 0 THEN
        SET newDate = DATE_ADD(timeEnd, INTERVAL 1 DAY);
    ELSE 
        SET newDate = timeEnd;
    END IF;

    RETURN newDate;
END$$
DELIMITER ;

This changes the delimiter to $$, adds $$ to the END statement of your function, and then reverts the delimiter back to ; (the default).

score 0 · Answer 2 · answered Nov 15 '19 at 09:59

You can use a simpler solution to solve this. There is no need to use a additional variable inside the function. You can return the values directly.

CREATE FUNCTION `CheckCreatedAt`(timeStart DATETIME, timeEnd DATETIME) RETURNS DATETIME
BEGIN
  IF TIMESTAMPDIFF(DAY, timeStart, timeEnd) <> 0 THEN
    RETURN DATE_ADD(timeEnd, INTERVAL 1 DAY);
  ELSE 
    RETURN timeEnd;
  END IF;
END

demo on dbfiddle.uk

score 0 · Answer 3 · answered Nov 15 '19 at 10:08

Sebastian, you could simplify the fuction even further. Remove the IF ELSE and multiple RETURNs and even the DATE_ADD (and the BEGIN/ELSE):

CREATE FUNCTION `CheckCreatedAt`(
timeStart DATETIME, 
timeEnd DATETIME
) 
RETURNS DATETIME
RETURN IF(TIMESTAMPDIFF(DAY, timeStart, timeEnd) != 0, timeEnd +INTERVAL 1 DAY, timeEnd);

Creating a mysql function to manipulate a date

3 Answers3