Re: Output yyyymmdd formatted date || 20030131 to FridayJanuary 31, 2003

Very nice John. I'm quickly learning the utility of MySql Date and Time objects. I used to have to write a ten line script to format the date; now, I can use this: SELECT DATE_FORMAT(mydate, '%a %M %d, %Y') and I'm done. Thanks a bunch for your help, John. I might even write an effiicient application in this lifetime............. --Noah "John W. Holmes" <holmes072000@charter.net> wrote in message news:000201c2cfd8$dcc2e180$7c02a8c0@coconut... > 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/ > > > > > > > > >