SQL date interval list rows last active at least 30 days after creation

[jack55]

Hi so i’m currently just testing and refreshing my knowledge of all languages. I was doing a test on this website however can’t get above 67% and it’s really bugging me as I’d love to know what I’m doing wrong. I can clearly see something is wrong, syntax error maybe?

Any help would be much appreciated. Thanks so much.

The link to the question:
Registrations | MySQL | TestDome

[r937]

@jack55

as I’d love to know what I’m doing wrong

does that image show what you submitted in those two spaces?

let me give you a hint on how to debug something like this

your WHERE clause has two expressions that it’s comparing

one is last_activity, and it’s already listed in the SELECT clause

the other is DATE(create_date,INTERVAL 30 DAY), and it is not included in the SELECT clause, so go ahead and add it…

… and then run your query first without the WHERE clause, so that you can see both values for last_activity and DATE(create_date,INTERVAL 30 DAY) for every row in the table

if what you see makes sense, then go ahead and add the WHERE clause back

[jack55]

Hi thanks for your reply. Really appreciate your help

@r937

does that image show what you submitted in those two spaces?

Yes, shown is the image is the current code I have:
SELECT name, create_date, last_activity FROM registrations WHERE last_activity >= DATE(create_date, INTERVAL 30 DAY);

In this the test from the link mentioned above we are unable to add or edit the SELECT clause apart from those two text-boxes.

---

Ok so i began debugging as suggested,

Tried this:
SELECT last_activity, DATE(create_date, INTERVAL 30 DAY) FROM registrations`

Got this error:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' INTERVAL 30 DAY) FROM registrations LIMIT 0, 25' at line 1

I tried it another way i usually do:
SELECT last_activity, DATE(create_date) FROM registrations`

It returned the data.

I attempted to add the WHERE clause back in however received a syntax error. To be honest I feel like this is probably the easiest SQL statement to solve and I’m simply being blind.

Thanks for your help again

[r937]

yeah, okay, i’ll save you further grief

what you want is DATEADD, not DATE

you want to add a date interval to create_date

DATE is used to extract a date from either a datetime or timestamp value

[jack55]

Thanks so much. Kicking myself haha, that really should’ve been something I tried earlier on when I was trying DATE_SUB.

Thanks again @r937