CODEWITHSUNDEEP
mysql
BernardA
BernardA

Reputation: 1523

MySQL add date fields and compare to today on where clause

I am struggling with this one and getting a syntax error.

Trying to get campaigns that are still ongoing, ie, where start_date + duration_days is >= than today.

I've also tried with DATE_ADD with the same result.

start_date is a DATE field, while duration_days is INT.

SELECT id,start_date,duration_days
                FROM `myTable`
                WHERE start_date + INTERVAL duration_days DAYS >= CURDATE()

Thanks

Upvotes: 0

Views: 57

Answers (2)

Niagaradad
Niagaradad

Reputation: 463

Change the interval to DAY not DAYS, that's what is throwing the error.

SELECT id,start_date,duration_days
            FROM `myTable`
            WHERE start_date + INTERVAL duration_days DAY >= CURDATE()

Upvotes: 1

JimNicholson
JimNicholson

Reputation: 351

You need to use the DATE_ADD and DATE_SUB functions to manipulate dates rather than the arithmetic operators:

SELECT id,start_date,duration_days
FROM `myTable`
WHERE DATE_ADD(start_date, INTERVAL duration_days DAYS) >= CURDATE()

Upvotes: 0

Related Questions