SQL LIMIT syntax error

Question

So here is the whole thing:

SELECT * FROM sometable LIMIT 5*DATEDIFF(NOW(), '2011-08-30'), 5

Error:

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 '*DATEDIFF(NOW(), '2011-08-30'), 5' at line 1

The problem is clearly that LIMIT does not accept anything but integer apparently. It won't accept 1+1 or (1+1) as value either. Is there a way around it?

And just so you don't have to go and try, 5*DATEDIFF(NOW(), '2011-08-30') works just fine.

I am trying to fix this answer..

This question might help you if you want to do it in SQL, but AFAIK, you can't make a calculation in the LIMIT clause with MySQL. — Vincent Savard
– Vincent Savard, Commented Aug 31, 2011 at 19:39
It looks like there really isn't a way around it, but there are other things you could do. For instance, run a separate query to select the datediff, save it as a variable in whatever programming language you are using, and plug it in to the query you have above. Either that or you can calculate the datediff by other means first (using php date functions for example) — Brian Glaz
– Brian Glaz, Commented Aug 31, 2011 at 19:42
Can't even use a server side var, so select @lim := datediff(...); select .... limit @lim,1 fails as well. — Marc B
– Marc B, Commented Aug 31, 2011 at 19:44

ajreal · Accepted Answer · 2011-08-31 19:39:28Z

2

NO, is not doable in plain mysql
(however, is possible via stored procedure, user defined function)

your case, it can easily replaced via a PHP call

$offset = 5*
        date_diff(new DateTime('now'), new DateTime('2011-08-31'))->format('%a');
$sql = "SELECT * FROM sometable LIMIT {$offset},5";

answered Aug 31, 2011 at 19:39

ajreal

47.4k11 gold badges97 silver badges119 bronze badges

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

Comments

Kevin · Accepted Answer · 2011-08-31 19:45:19Z

1

The limit must be an integer or local variable. From the MySQL docs:

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants, with these exceptions:

Within prepared statements, LIMIT parameters can be specified using ? placeholder markers.

Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables as of MySQL 5.5.6.

answered Aug 31, 2011 at 19:45

Kevin

56.6k15 gold badges107 silver badges139 bronze badges

Collectives™ on Stack Overflow

SQL LIMIT syntax error

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related