Trying to find all records in database that are tommorows date using:
SELECT * FROM timeslots WHERE timeslot = DATE_ADD(now(), INTERVAL 1 DAY)
It doesnt apear to bring back any records even though there should be a match… Is this a problem with the field containing date/time aposed to just date?
Basically I want to be able to send out reminder for all appointments for tomorrow’s date… So I need to check against the date part of date/time for any records holding tomorrow’s date taking the time out of the equation and send the reminder…
The problem I have is that my server date and time doesnt match my countrys date and time.
Timeslot is formted as date/time
It would be best if I can just check against the date part of timeslot within mysql query for records that are tomorrows date so there is no discrepancy with the time difference between server dates and dates stored in database…
NOW includes hours, minutes, seconds and microseconds… as such doing a == is not going to work unless you have a record that is EXACTLY 24 hours from now.
What you want to do is compare from now to then… more specifically the BETWEEN statement.
SELECT * FROM timeslots
WHERE timeslot BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 1 DAY)
That should do it. Same thing as
SELECT * FROM timeslots
WHERE timeslot >= NOW() AND timeslot <= DATE_ADD(NOW(), INTERVAL 1 DAY)
Though that assumes timeslot is a mysql dateTime or timestamp value. If it’s type date, then you could pull tomorrow using DATE instead of NOW. Which one to use comes down to do you want any from tomorrows date, or do you want them from the next 24 hours? There is a difference after all.
– edit –
IF timeslot is type DATE (and therein has no time attached) and you want just the ones from tomorrow’s date:
SELECT * FROM timeslots
WHERE timeslot = DATE(DATE_ADD(NOW(), INTERVAL 1 DAY))
As the DATE function strips the time off the DATE_ADD result.
If now() is June 1st, 2001 at 3:46pm and 23 seconds, then DATE_ADD(now(), INTERVAL 1 DAY) will only return June 2nd, 2001 at 3:46pm and 23 seconds. I think that you want to use DATEPART to extract today’s day and tomorrow’s day. Check out www.w3schools.com/sql/func_datepart.asp for some examples.