Fetch record from current date to next whole month(till last date of next month) mysql

Question

i.e

Suppose the current date is 18/10/2016 then i want to fetch all record from 18/10/206 to 30/11/2016

This is the scenario, please help me to get effective way to fetch this from in a single query mysql.

What do you mean by `next whole month`? Next 30 days or Next month 1-28/29/30/31? — Irvin, Oct 18 '16 at 06:41
from current date to next month's last date, as i have explained in question with an example, you did not seen? — Jigar7521, Oct 18 '16 at 06:45

score 3 · Accepted Answer · answered Oct 18 '16 at 06:51

3

You can use BETWEEN to compare dates.

Take help of LAST_DAY(date) function.

SELECT 
* 
FROM your_table 
WHERE your_date BETWEEN CURDATE() AND LAST_DAY(CURDATE() + INTERVAL 1 MONTH)

Note:

LAST_DAY() function

MySQL LAST_DAY() returns the last day of the corresponding month for a date or datetime value. If the date or datetime value is invalid, the function returns NULL.

answered Oct 18 '16 at 06:51

1000111

13,169
2
28
37

Sorry to say but this is giving a syntax error – Jigar7521 Oct 18 '16 at 07:41
Please share the query you tried along with the error you encountered. – 1000111 Oct 18 '16 at 07:45
This is the error : SQL query: Documentation SELECT * FROM members_detail Birthdate BETWEEN CURDATE() AND LAST_DAY(CURDATE() + INTERVAL 1 MONTH) LIMIT 0, 25 MySQL said: Documentation #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 'BETWEEN CURDATE() AND LAST_DAY(CURDATE() + INTERVAL 1 MONTH) LIMIT 0, 25' at line 1 – Jigar7521 Oct 18 '16 at 07:49
1

Sorry there are missing a where keyword, and that was of my mistake – Jigar7521 Oct 18 '16 at 07:50
I see you didn't use `WHERE` – 1000111 Oct 18 '16 at 07:51

score 1 · Answer 2 · answered Oct 18 '16 at 06:51

1

$time = strtotime(date('Y-m-d'));
$final = date("Y-m-d", strtotime("+1 month", $time));
$nextMonth= date('t/m/Y',strtotime($final));
$now=date('d/m/Y');
$sql="select * from table where date>='".$now.' and date<=$nextMonth";
$res=mysql_query($sql);

answered Oct 18 '16 at 06:51

Pradyut Manna

588
1
3
12

First priority was to use only mysql if possible, otherwise we can use php, but thanks buddy :) – Jigar7521 Oct 18 '16 at 08:03

Fetch record from current date to next whole month(till last date of next month) mysql

2 Answers2