MySQL - Fetch data 30 days before its expiration date

Question

I'm trying to fetch data whose expiration date is within 30 days of today. I've tried using BETWEEN clause but still not working.

table name registration:

id               exp_date

12                05-20-2018
19                05-19-2018
34                05-22-2018

let say the date today is 04-28-2018. Which I stored in a variable $date_today

$date_today = '04-28-2018';
$query = "SELECT * FROM registration WHERE expiration_date BETWEEN('$date_today', DATE_SUB(expiration_date, INTERVAL 30 DAY)";
        $test =  mysqli_query($con, $query);
        $row = mysqli_fetch_assoc1( $test);

and this the error that I'm getting:

Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given

Could you try using `BETWEEN ... AND ...` but swap the dates around, so the earlier date is on the left. Not sure if the order of the dates matters though. — Nigel Ren, Apr 28 '18 at 06:36
Which rows are you trying to match? Which ones shouldn't be returned? — Barmar, Apr 28 '18 at 08:18

Barmar · Accepted Answer · 2018-04-28T09:09:43.377

4

BETWEEN is not a function, it's an operator. The syntax is:

BETWEEN low_value AND high_value

You should also stop using variable substitution and use prepared statements with mysqli_stmt_bind_param(). See How can I prevent SQL injection in PHP?

To get everything that's expiring within the next 30 days, you want:

WHERE exp_date BETWEEN CURDATE() and DATE_ADD(CURDATE(), INTERVAL 30 DAY)

Notice that this uses DATE_ADD(), not DATE_SUB(), since you want expiration dates in the future, not the past.

edited Apr 28 '18 at 09:09

answered Apr 28 '18 at 06:18

Barmar

741,623
53
500
612

thanks for that, error was removed but still no data – Drenyl Apr 28 '18 at 06:27
@Barmar I need to get the data or the `id` 30 days before it's expiration date. Meaning today `april 28, 2018` and the expiration is `may 10, 2018` .The query should get the id which expiration date is `may 10, 2018` – Drenyl Apr 28 '18 at 09:06
You mean everything that is expiring between today and May 28? – Barmar Apr 28 '18 at 09:08
@Barmar working fine! thanks, will review the functions given here – Drenyl Apr 28 '18 at 09:27

score 0 · Answer 2 · edited May 23 '21 at 05:34

0

For sql server,to check items which will expire within a period of 1-7days

select sdate, edate, DATEDIFF(MONTH,GETDATE(),edate) as MONTHS from Stockin 
where DATEDIFF(MONTH,GETDATE(),edate) between 1 and 7
order by edate desc

enter image description here

edited May 23 '21 at 05:34

Simas Joneliunas

2,890
20
28
35

answered May 23 '21 at 01:22

Benard Kim

1

Azeem Hafeez · Answer 3 · 2018-04-28T06:36:46.283

-1

I hope it will work for you.

DATEDIFF(d,GETDATE(),expiration_date) >= 30

edited Apr 28 '18 at 06:36

answered Apr 28 '18 at 06:30

Azeem Hafeez

250
4
14

It should be `<=`. But you also have to check that it's `>= 0`, he doesn't want expiration dates in the past. – Barmar Apr 28 '18 at 08:10
if you look at question he is asking 30 days before expiry – Azeem Hafeez Apr 28 '18 at 08:17
None of the dates in his sample data will be matched by your answer. – Barmar Apr 28 '18 at 08:19
Your code matches *more* than 30 days before expiry. – Barmar Apr 28 '18 at 08:19
he can change it to his requirement from >=30 to =30. – Azeem Hafeez Apr 28 '18 at 08:23
Since he's using `BETWEEN`, I think he means "within 30 days", which is `<=`. – Barmar Apr 28 '18 at 08:25
"Fetch data 30 days before its expiration date" this is what he is asking, not within 30 days. – Azeem Hafeez Apr 28 '18 at 08:27
i don't think so if you give me -1 for the answer. Because it is very clear from his question that he wants rows 30 days before its expiration date. – Azeem Hafeez Apr 28 '18 at 08:32
I don't know why someone downvoted, maybe it's just because the answer posts code without any explanation of the original problem or how this solves it. Code without explanation is a poor answer. – Barmar Apr 28 '18 at 08:34
He's clarified the question, he means expiring sometime in the next 30 days. – Barmar Apr 28 '18 at 09:12

MySQL - Fetch data 30 days before its expiration date

3 Answers3