CODEWITHSUNDEEP
mysqlsql
Reuben Ehrensberger
Reuben Ehrensberger

Reputation: 15

SQL query to select rows on date added where when they expired in a field in the same table

I have a table that has a date_added column and an expiry time, set in days.

I have tried the following:

select * 
from `claims` 
where date_added(`date_added`, interval `expire_period` day) < date(now())

I have tried reformatting and still getting an error.

This is solved:

SELECT * FROM dk.claims WHERE DATE_ADD(date_added, interval expire_period day) < date(now());

Upvotes: 1

Views: 493

Answers (2)

simbabque
simbabque

Reputation: 54333

Your SQL syntax is wrong. There is no function date_added. What you need is date_add.

SELECT * 
FROM claims 
WHERE DATE_ADD(date_added, INTERVAL expire_period DAY) < DATE(NOW());

Your error message will have told you something like:

SQL ERROR (1064): You have an error in your SQL syntax; check the manual that correcsponds to your MySQL server version for the right syntax to use near ') < date(now())' at line 1.

So it did not know what to do with the closing bracket. From there, you can track back to see where the open bracket is. Next to date_added. And that is not a function.

You can also rely on the database client. I'm using HeidiSQL on Windows, and it highlights all keywords (including functions). The date_added is not blue in your query.

Upvotes: 1

Aziz Shaikh
Aziz Shaikh

Reputation: 16524

Try this:

select * 
from claims 
where date_add(date_added, interval expire_period day) < date(now())

Reference of DATE_ADD

Upvotes: 1

Related Questions