CODEWITHSUNDEEP
mysql
user3377911
user3377911

Reputation: 11

MySQL Query - SELECT all records where current date is in between a date_to and last 30 day of that date_to

Hello everyone I hope you can help me on this

I have a table m_membership_list and there are fields date_from and date_to and I need to count the number of the records where current date is between date_to and the last_30 days of the the date_to.

I searched about this but I did not find the exact example but I think the logic is like in MySQL Query - Records between Today and Last 30 Days

But still I can't get it.

Here is my SQL.

SELECT * FROM m_membership_list WHERE NOW() IN DATE_SUB(date_to, INTERVAL 30 DAY)

I think the logic is almost like this but i dont know why it is not working.

SELECT * FROM m_membership_list WHERE NOW() BETWEEN date_to AND (DATE_SUB(date_to,INTERVAL 30 DAY))Improv

Upvotes: 0

Views: 254

Answers (3)

changec
changec

Reputation: 51

try this

SELECT count(*)
FROM m_membership_list 
WHERE TO_DAYS( NOW( ) ) - TO_DAYS( date_to) <=30

Upvotes: 0

Sadikhasan
Sadikhasan

Reputation: 18600

Try this

SELECT *
FROM m_membership_list
WHERE NOW() BETWEEN NOW()-INTERVAL 30 DAY AND date_to;

Upvotes: 0

eggyal
eggyal

Reputation: 125835

As documented under expr BETWEEN min AND max:

If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1, otherwise it returns 0.

Since NOW() cannot simultaneously be greater than or equal to date_to and less than or equal to DATE_SUB(date_to,INTERVAL 30 DAY), your clause is necessarily always false. Reverse the operands:

WHERE NOW() BETWEEN DATE_SUB(date_to,INTERVAL 30 DAY) AND date_to

Upvotes: 1

Related Questions