RE: [PHP] Output yyyymmdd formatted date || 20030131 to FridayJanuary 31, 2003

SELECT * FROM table WHERE date BETWEEN 20030201 AND 20030201 + INTERVAL 7 DAY I assume '20030201' will come from PHP eventually, right, or the current date? For any record between now and 7 days from now: SELECT * FROM table WHERE date BETWEEN CURDATE() AND CURDATE() + INTERVAL 7 DAY For a date from PHP, $date = '20030201'; SELECT * FROM table WHERE date BETWEEN $date AND $date + INTERVAL 7 DAY ---John W. Holmes... PHP Architect - A monthly magazine for PHP Professionals. Get your copy today. http://www.phparch.com/ > -----Original Message----- > From: Noah [mailto:noah@newminddesign.com] > Sent: Saturday, February 08, 2003 10:20 PM > To: holmes072000@charter.net; operations@cdmsports.com > Cc: php-general@lists.php.net > Subject: Re: [PHP] Output yyyymmdd formatted date || 20030131 to > FridayJanuary 31, 2003 > > Right. > > I've switched the date column from type INT to type DATE in our MySql db. > > The problem I've had with retrieving records in a certain date range with: > > SELECT * FROM table WHERE yourdate BETWEEN 20030201 AND 20030207 > > is getting the latter part of the expression; i.e. in this case 20030207 > to > be seven days "older" than the first part. > > This is where I need to use MySql's DATE_ADD, and other date manipulation > functions.......... > > Lots to learn; little time to do it. > > Thanks for feedback, John. > > > --Noah > > > ----- Original Message ----- > From: "John W. Holmes" <holmes072000@charter.net> > To: "'Noah'" <noah@newminddesign.com>; > <operations@cdmsports.com> > Cc: <php-general@lists.php.net> > Sent: Saturday, February 08, 2003 3:31 PM > Subject: RE: [PHP] Output yyyymmdd formatted date || 20030131 to > FridayJanuary 31, 2003 > > > > > The dates are stored in a MySql db. > > > > > > I checked out the MySql DATE_FORMAT function -- pretty cool. > > > > > > However, pardon my ignorance here, how can I do date comparisons? > > > > > > For example, if I want to retrieve records from the db where the date > > is > > > between say, 2003-02-01 and 2003-02-07, will MySql be able to compare > > the > > > strings? > > > > > > I stored my dates as integer fields to do such a comparison, but it > > looks > > > like I need to graduate to MySql date time functions.......... > > > > If you've done it correctly and stored your dates in a MySQL DATE, > > DATETIME, or TIMESTAMP column, then you can do something like this: > > > > SELECT * FROM table WHERE yourdate BETWEEN 20030201 AND 20030207 > > > > If you're storing them in an INT column, then change it over to one of > > the above. > > > > Go back to the manual and read about date_sub() and date_add() in MySQL > > for further date manipulation... > > > > ---John W. Holmes... > > > > PHP Architect - A monthly magazine for PHP Professionals. Get your copy > > today. http://www.phparch.com/ > > > >