Date

Databases
1

Hi,

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?

Thanks

2

Badly put I guess…

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…

3

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…

4

Yup, Very nice – though that makes me ask “then why do we have date_add in the first place?”

Though… I thought the addition converted it back into a full timestamp instead of just the date?

5

a date plus a day interval yields a date, not a timestamp

and the DATE_ADD function probably just predates (no pun intended) the date arithmetic, i dunno, i’m too lazy to look it up

6

you can simplify this in two ways

first, you can avoid the DATE() function, which extracts the date from a datetime value, by not using NOW() and instead using CURRENT_DATE

then you can avoid the DATE_ADD function by using explicit date arithmetic

so…

WHERE timeslot = CURRENT_DATE + INTERVAL 1 DAY

much nicer, eh?

:cool:

7

Thanks something to think about and it worked thanks…

8

I had a play with DATE_FORMAT which looked like it should work using:

SELECT DATE_FORMAT(timeslot, '%Y-%m-%d') AS timeslot FROM timeslots WHERE timeslot = DATE_ADD(curdate(), INTERVAL 1 DAY)

Still no results… Is this along the right track to make it work?

9

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.

See:
http://dev.mysql.com/doc/refman/5.6/en/date-and-time-types.html

10

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.

Hope this helps…