SQL function to check multiple condition with datetime column and perform addition based on conditio-CodePudding

I am trying to make sql function to perform calculation as per below logic. I am using phpmyadmin and this is what I tried so far. I am geeting syntax error.

Logic I want to implement :

if((column A == null) && (column B == null)){

                 return null;
             }

             var C = moment(column A).add(98, 'days'); 
             var D = moment(column B).add(98, 'days'); 

            

              
                if(column B == null)
                {
                    return C.format('DD MMM YYYY'); // Add 98 Days 
                }

        
                else     
                {
                    return D.format('DD MMM YYYY'); // Add 98 Days
                }

   
            }

This is what I done so far:

CREATE FUNCTION TssaForecast(A DATETIME, B DATETIME) RETURNS DATETIME
BEGIN

  DECLARE TID DATETIME;
    

IF ( (A is NULL) AND (B is NULL) ) THEN
    SET TID = ' ';

ELSE IF (A is NULL) THEN
    SET TID = DATE_ADD( B, INTERVAL 98 DAY);
    
ELSE 
    SET TID = DATE_ADD( A, INTERVAL 98 DAY);
    
END IF;

    RETURN TID;
    
END

Please advice what is wrong in this code. Thank you.

CodePudding user response：

Your logic in code and SQL is different. In your code B is preferred over A if both A and B are supplied, whereas your SQL code prefers A over B.

In any case, you don't really need a function for this, it's fairly simple: DATE_ADD(COALESCE(A, B), INTERVAL 98 DAY) (here A is preferred over B).

COALESCE will return first non-NULL argument it is supplied or NULL if all of them are NULL and then 98 will get added to that (adding an interval to NULL will result in a NULL).

If you need it as a function really then:

CREATE FUNCTION TsaaForecast(A DATETIME, B DATETIME)
RETURNS DATETIME DETERMINISTIC
RETURN DATE_ADD(COALESCE(A, B), INTERVAL 98 DAY)

db<>fiddle here