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

Question

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

score 1 · Answer 1 · answered May 13 '14 at 08:13

1

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

answered May 13 '14 at 08:13

eggyal

122,705
18
212
237

thanks for your comment but i encountered this to xammp Illegal mix of collations (latin1_swedish_ci,NUMERIC), (utf8_general_ci,COERCIBLE), (latin1_swedish_ci,IMPLICIT) for operation 'between' – user3377911 May 14 '14 at 06:22

score 0 · Answer 2 · answered May 13 '14 at 08:14

0

Try this

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

answered May 13 '14 at 08:14

Sadikhasan

18,365
21
80
122

2

This will fetch ALL records from the table. – Ravinder Reddy May 13 '14 at 08:15
Hello i already try that but the problem is it still selecting all the data before the 1month before the date_to – user3377911 May 14 '14 at 06:15

score 0 · Answer 3 · answered May 13 '14 at 08:15

0

try this

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

answered May 13 '14 at 08:15

changec

51
5

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

3 Answers3