89

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.

Improve this question

7 Answers 7

Reset to default
155

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

@phpBOY: I think you missed the ` = CURRENT_DATE` part.
i tried this select * from fab_scheduler where custid = 1334666058 and DATE(DATE_ADD(eventdate, INTERVAL 1 DAY)) = CURRENT_DATE but now it returns empty result set :(
I would use CURDATE() personally
@phpBOY: You're right. You have to use -1 to match with CURRENT_DATE. Try with the updated query.
@Daniel Vassallo: Could you please construct the query, iam getting empty record set. Thanks
|
10

It`s possible to use MySQL specific syntax sugar:

SELECT ... date_field + INTERVAL 1 DAY

Looks much more pretty instead of DATE_ADD function

Improve this answer

Comments

9

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))
Improve this answer

1 Comment

I don't think that can work. eventdate will never be equal to eventdate + 1 day. I think you intended to use CURDATE() in there.
2

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;

Improve this answer

Comments

1

You can try this:

SELECT DATE(DATE_ADD(m_inv_reqdate, INTERVAL + 1 DAY)) FROM  tr08_investment
Improve this answer

Comments

0

How about this: 

select * from fab_scheduler where custid = 1334666058 and eventdate = eventdate + INTERVAL 1 DAY
Improve this answer

Comments

-3 
$date = strtotime(date("Y-m-d", strtotime($date)) . " +1 day");

Or, simplier:

date("Y-m-d H:i:s", time()+((60*60)*24));
Improve this answer

1 Comment

You didn't count on daylight savings time. mktime would be better.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.