MySQL: How to add one day to datetime field in query

In my table I have a field named eventdate in datetime format like 2010-05-11 00:00:00.

How do i make a query so that it adds one day to the eventdate eg if today is 2010-05-11, i want to show in where clause to return all records with tomorrow's date.

Update:

I tried this:

select * from fab_scheduler where custid = 1334666058 and DATE_ADD(eventdate, INTERVAL 1 DAY)

But unfortunately it returns the same record even if i add an interval greater than 1.

Result:

2010-05-12 00:00:00

But i only want to select records with tomorrow's date.

Solution 1:

You can use the DATE_ADD() function:

... WHERE DATE(DATE_ADD(eventdate, INTERVAL -1 DAY)) = CURRENT_DATE

It can also be used in the SELECT statement:

SELECT DATE_ADD('2010-05-11', INTERVAL 1 DAY) AS Tomorrow;
+------------+
| Tomorrow   |
+------------+
| 2010-05-12 |
+------------+
1 row in set (0.00 sec)

Solution 2:

It`s possible to use MySQL specific syntax sugar:

SELECT ... date_field + INTERVAL 1 DAY

Looks much more pretty instead of DATE_ADD function

Solution 3:

Have a go with this, as this is how I would do it :)

SELECT * 
FROM fab_scheduler
WHERE custid = '123456'
AND CURDATE() = DATE(DATE_ADD(eventdate, INTERVAL 1 DAY))

Solution 4:

If you are able to use NOW() this would be simplest form:

SELECT * FROM `fab_scheduler` WHERE eventdate>=(NOW() - INTERVAL 1 DAY)) AND eventdate<NOW() ORDER BY eventdate DESC;

With MySQL 5.6+ query abowe should do. Depending on sql server, You may be required to use CURRDATE() instead of NOW() - which is alias for DATE(NOW()) and will return only date part of datetime data type;