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

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

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

Hi thanks for your reply. Really appreciate your help :slight_smile:

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

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

1 Like

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 :slight_smile:

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.