Search code examples
phpsqllimit

SQL LIMIT by end of the month

I have some code with me:

$sql = "SELECT * FROM palash ORDER BY id DESC LIMIT 31";

I want the LIMIT 31 to be LIMIT END OF THE MONTH

Solution

  • Use the LAST_DAY() function.

    Takes a date or datetime value and returns the corresponding value for the last day of the month. Returns NULL if the argument is invalid.

    mysql> SELECT LAST_DAY('2003-02-05');
        -> '2003-02-28'
mysql> SELECT LAST_DAY('2004-02-05');
        -> '2004-02-29'
mysql> SELECT LAST_DAY('2004-01-01 01:01:01');
        -> '2004-01-31'
mysql> SELECT LAST_DAY('2003-03-32');
        -> NULL

    In your case do this:

    SELECT * 
FROM palash 
WHERE your_date_column <= LAST_DAY(CURDATE()) 
ORDER BY id DESC

    Avoid solutions like OlivierH's (no offense, Olivier). Functions applied on your columns make it impossible for MySQL to use an index.