15,962,206 members
See more:
I had wrote the below query because I wanted to return the list of every user that joined when the interval was 9 days using curdate() and joineddate. For example if today was 6/30

user1 | 6/21
user2 | 6/20
user3 | 6/12
user4 | 6/03
user5 | 6/01

But I realized that this only returns 1 date (user1)

How can this return users (user1, user3, user4) since they met the 1 week criteria

SQL
`SELECT * FROM users where (DATEDIFF(CURDATE(),joineddate) = 9)`

What I have tried:

I have tried using the interval function and datediff
Posted
Updated 8-Jul-24 6:44am
v2

## Solution 1

The problem is you're subtracting the joined date from the current date and seeing if it is EQUAL to 9. That will only ever match a single date. In your example, that would be everyone who joined only on 6/21, or (6/30 - 6/21 = 9).

If you're looking for everyone who joined in the last 9 days, you have to use a different comparison operator, less than or equal to, or <=.
SQL
`SELECT * FROM users where (DATEDIFF(CURDATE(),joineddate) <= 9)`

mcbain19 8-Jul-24 12:58pm
Thanks Dave, but I am looking for an every 9 day interval so return user if joineddate condition matches (9 days, 18 days, 27 days, 36 days... starting from joineddate)
Dave Kreskowiak 8-Jul-24 13:17pm
That isn't very clear. Are you saying you want the users who joined exactly 9 days ago, or between now and 9 days ago, or 9 to 18 days ago, and so on?
Dave Kreskowiak 8-Jul-24 14:05pm
OK, rereading this, I think I understand now. Wendelius has the answer that would work.

## Solution 2

Perhaps something like
SQL
`SELECT * FROM users WHERE MOD(DATEDIFF(CURDATE(), joineddate), 9) = 0`