CODEWITHSUNDEEP
mysql
Ewan Delanoy
Ewan Delanoy

Reputation: 1282

What is wrong with the syntax of this MySQL query on dates?

phpMyAdmin tells me that the following code snippet is syntactically incorrect :

 SET @time := NOW();
 SET @timeset :=0;


 IF @timeset=1 
    THEN SET @time := DATE_ADD(@time,INTERVAL 10 SECOND);
    ELSE SET @time := DATE_SUB(NOW(),INTERVAL 1 DAY);
         SET @timeset := 1; 
    END IF;

but I fail to see what's wrong with it.

The exact error message goes : enter image description here

UPDATE : thanks to wolfgangalther's answer, I found the following workaround :

SET @time := NOW();
SET @timeset :=0;
SET @time=IF(@timeset=1,DATE_ADD(@time,INTERVAL 10 SECOND),DATE_SUB(NOW(),INTERVAL 1 DAY)); 
SET @timeset:=1;

Upvotes: 1

Views: 126

Answers (2)

mahesh
mahesh

Reputation: 1331

This kind of IF ELSE you can only use in stored procedures, But you can use If condition in query as given below.

IF(expr,if_true_expr,if_false_expr)


Example

SELECT customerNumber,
       customerName,
       IF(state IS NULL,'N/A',state) state,
       country
FROM customers;

Upvotes: 0

wolfgangwalther
wolfgangwalther

Reputation: 1236

From the MySQL manual:

MySQL supports the IF, CASE, ITERATE, LEAVE LOOP, WHILE, and REPEAT constructs for flow control within stored programs.

You are not allowed to issue an IF construct on its own!

There is also a short discussion about it here (including some alternatives): https://dba.stackexchange.com/questions/80132/using-if-statement-outside-of-a-stored-procedure-or-function

Note: Using an IF statement at that place has been requested as a feature in the past, but is not implemented into MySQL as of now.

Edit:
Stored programs are either stored procedures or stored functions. Stored functions are the same thing as stored procedures, they just return a value.

Read more about stored procedures:

Upvotes: 3

Related Questions