0

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..

Community
  • 1
  • 1
Bojan Kogoj
  • 5,321
  • 3
  • 35
  • 57
  • 1
    [This question](http://stackoverflow.com/questions/5872667/how-to-make-limit-offset-dynamic-using-only-mysql) 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 Aug 31 '11 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 Aug 31 '11 at 19:42
  • Can't even use a server side var, so `select @lim := datediff(...); select .... limit @lim,1` fails as well. – Marc B Aug 31 '11 at 19:44

2 Answers2

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";
ajreal
  • 46,720
  • 11
  • 89
  • 119
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.

Kevin
  • 53,822
  • 15
  • 101
  • 132