MYSQL -Incorrect Syntax

Question

WHILE x > 1 DO
SET x = x - 1;    
SET totalTime =  SELECT CONCAT(FLOOR(HOUR(TIMEDIFF(end_time,start_time)) / 24), ' days ',

MOD(HOUR(TIMEDIFF(end_time,start_time)), 24), ' hrs ',

MINUTE(TIMEDIFF(end_time,start_time)), ' minutes ') AS total_Time

I don't see why I am having a syntax error?

It is part of a bigger procedure but is pointing to this aas being incorrect

Error message: SQL Error (1064): 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 'SELECT CONCAT(FLOOR(HOUR(TIMEDIFF(end_time,start_time)) / 24,' days',' at line 11

and totalTime is declared as a VARCHAR(50)

For starters there's no end while; (but that might be caused by the fact that you're just quoting part of a procedure). And what's the error you get? — fvu
– fvu, Commented Dec 2, 2012 at 22:45
1064 in general means some error in terms of so called "reserved words" — sailingthoms
– sailingthoms, Commented Dec 2, 2012 at 22:52

KaeL · Accepted Answer · 2012-12-03 02:13:13Z

1

You do not need to use the SELECT keyword if you are setting the value of a variable.

SET totalTime = CONCAT(FLOOR(HOUR(TIMEDIFF(end_time,start_time)) / 24), ' days ',

MOD(HOUR(TIMEDIFF(end_time,start_time)), 24), ' hrs ',

MINUTE(TIMEDIFF(end_time,start_time)), ' minutes ');

If you want to use the SELECT keyword, then the correct syntax is:

SELECT CONCAT(FLOOR(HOUR(TIMEDIFF(end_time,start_time)) / 24), ' days ',

MOD(HOUR(TIMEDIFF(end_time,start_time)), 24), ' hrs ',

MINUTE(TIMEDIFF(end_time,start_time)), ' minutes ') INTO totalTime;

Take note of the added code INTO variable name.

answered Dec 3, 2012 at 2:13

KaeL

3,6612 gold badges31 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sailingthoms · Accepted Answer · 2012-12-02 23:06:12Z

0

I think it might be, because FLOOR, HOUR, TIMEDIFF etc need a surrounding SELECT like:

WHILE x > 1 DO
SET x = x - 1;    
SET totalTime =  SELECT CONCAT( SELECT FLOOR( SELECT HOUR( SELECT TIMEDIFF(end_time,start_time)) / 24), ' days ',

SELECT MOD( SELECT HOUR( SELECT TIMEDIFF(end_time,start_time)), 24), ' hrs ',

SELECT MINUTE(SELECT TIMEDIFF(end_time,start_time)), ' minutes ') AS total_Time

Otherwise it may not interprete this Words as kind of function calls and throw "reserved words" error. (I assume the END WHILE; is already somewhere in your code.) Please check it and let me know.

edited Dec 2, 2012 at 23:06

answered Dec 2, 2012 at 23:00

sailingthoms

94810 silver badges22 bronze badges

Collectives™ on Stack Overflow

MYSQL -Incorrect Syntax

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related