To you all, Thanks for your input. The "TO_DAYS(date_col)%4 = TO_DAYS('2006-4-17')% 4" where statement worked perfectly. And, thanks for the reference to the manual. It can be a bit daunting for a newbie.
Bob Cooper > Dan: > > Thanks! > > Jim > > > In the last episode (Mar 18), Jim Ginn said: > >> > In the last episode (Mar 16), Bob Cooper said: > >> >> I am working with MySQL ver 5.1 on a Ubuntu Linux x86_64. I am > new > >> >> to both SQL and MySQL. I have been able to query out most of the > >> >> data I need from my tables without any issues but his one has > >> >> stumped me. > >> >> > >> >> I am trying to query data associated with specific dates. The > >> >> dates are not sequential but somewhat sporadic. I would like to > >> >> query out data/dates that are every 4 days from a starting date. > >> >> > >> >> 2006-4-17, 2006-4-21, etc. > >> >> > >> >> I have tried ADDDATE('2006-4-14',interval 4 day)<=Date_col but > it > >> >> give me only the next date 2006-4-21. > >> > > >> > You could do it b > > y converting to a daynumber (the number of days > >> > since year 0) and doing modulo arithmetic: > >> > > >> > WHERE TO_DAYS(date_col)%4 = TO_DAYS('2006-4-17')%4 > >> > > >> > > http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_to-days > >> > >> I've used the TO_DAYS on a SELECT statement ie: > >> > >> SELECT * FROM properties WHERE > >> (TO_DAYS(NOW()) - TO_DAYS(CreationDate) = 1) ORDER BY id DESC > >> > >> however it didn't seem to take advantage or use the index on that > field > >> (ie. CreationDate) ... > > > > Right; mysql needs CreationDate all by itself on one side of a > > comparison operator to be able to use an index. In your case, try > > > > WHERE CreationDate = CURDATE() - INTERVAL 1 DAY > > > > , assuming CreationDate is a 'date' field type. If it's a datetime, > > you'll need to use a BETWEEN operator and cover the time range from > > midnight to midnight on your target day. > > > > -- > > Dan Nelson > > [EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]