CODEWITHSUNDEEP
sqlmysqldate
Gondim
Gondim

Reputation: 3048

Subtracting dates in where clause

I need to make a select with the past 60 days starting in the present day. For example:

select * from product
where product.expire_date = ?1 - 60

?1 is a parameter with today's date.

I can't make it work, what am I doing wrong?

I use MySQL

Upvotes: 1

Views: 2814

Answers (2)

Riedsio
Riedsio

Reputation: 9926

I think this is what you'd like.

SELECT * 
FROM product
WHERE product.expire_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 60 DAY) AND CURDATE()

Note that BETWEEN is inclusive.

NB, if your expire_date is a datetime field and not a date field, you might have to modify it a bit (i.e. CONCAT() the appropriate time strings to the references on the RHS of the BETWEEN, or throw a DATE() around the LHS of the BETWEEN (but then you would no longer use an index on expire_date, assuming there was one.

Upvotes: 1

Spredzy
Spredzy

Reputation: 5174

Take a look at this : http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-sub

For more help : WHERE date BETWEEN dateX AND date_sub(dateX, 60days). I think that what you want right ?

Upvotes: 0

Related Questions