Nia Nia - 3 months ago 10
MySQL Question

Why is "INTERVAL" not a valid syntax in IF statement

I have a

IF THEN ELSE
statement in one of my stored prodecures.

if (DAYOFWEEK((SELECT DATE_ADD(_todaydate, INTERVAL (_sign * offset.value) DAY)))=7)
then
SELECT DATE_ADD(daytemp, INTERVAL (_sign * offset.value + 2) DAY) into `day`;
elseif (DAYOFWEEK((SELECT DATE_ADD(_todaydate, INTERVAL _sign * offset.value DAY)))=1)
then
SELECT DATE_ADD(daytemp, INTERVAL (_sign * offset.value + 1) DAY) into `day`;
else
Select (SELECT DATE_ADD(_todaydate, INTERVAL _sign * offset.value DAY)) into `day`;
END if;


where
_todaydate
is today's date,
_sign
is either
+1
or
-1
depending on if you want to find days in future or past and the
offset
is a number int showing how many days from
_todaydate
.

The query is meant to return me working day either in past or future depending upon the
_sign * offset.value
, but the problem is that sql returns me an error saying that
"unexpected INTERVAL (interval) in the if statement"


I am not sure why I am getting this problem because the query it self looks fine to me. Can some one please see what I am missing...

Additional Information:

I have this IF statement in a "Select From" statement, Could that be the case of that this error is happening?

Answer

Wouldn't the code be much easier to debug and understand, if we avoided repeating long expressions, wrote something like this (to accomplish what it looks like we are trying to achieve):

  DECLARE _dow INT;
  DECLARE _inc INT;

  SELECT DAYOFWEEK(DATE_ADD(_todaydate, INTERVAL (_sign * offset.value) DAY)) INTO _dow ;

  CASE _dow
    WHEN 7 THEN SET _inc = 2;
    WHEN 1 THEN SET _inc = 1;
    ELSE        SET _inc = 0;
  END CASE;  

  SELECT DATE_ADD(daytemp, INTERVAL (_sign * offset.value + _inc) DAY) INTO `day`;

To answer the question you asked, "why is INTERVAL not a valid syntax in IF statement"...

I'm not aware of any restriction in MySQL stored programs that prohibits the use of the keyword INTERVAL within an IF statement. Obviously, the INTERVAL keyword isn't part of the IF construct; the keyword is only valid in the context of some expressions.


FOLLOWUP

I notice that the rewrite above is not equivalent to the original. In the original, under the ELSE condition, it's DATE_ADD(_todaydate,. That differs from the other two conditions, where it's DATE_ADD(daytemp

A slight re-write to accommodate that difference, adding another variable _bdt, allows us to still just use two SELECT statements...

  DECLARE _bdt DATE;
  DECLARE _dow INT;
  DECLARE _inc INT;

  SELECT DAYOFWEEK(DATE_ADD(_todaydate, INTERVAL (_sign * offset.value) DAY)) INTO _dow ;

  CASE _dow
    WHEN 7 THEN 
      SET _inc = 2;
      SET _bdt = daytemp; 
    WHEN 1 THEN
      SET _inc = 1;
      SET _bdt = daytemp;
    ELSE
      SET _inc = 0;
      SET _bdt = _todaydate;
  END CASE;  

  SELECT DATE_ADD(_bdt, INTERVAL (_sign * offset.value + _inc) DAY) INTO `day`;