Thanks to all for the responses, as always problem now solved and I learnt a few things.
Regards John Berman -----Original Message----- From: Chris Sansom [mailto:[EMAIL PROTECTED] Sent: 23 July 2006 16:02 To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: Struggling with the logic At 15:43 +0100 23/7/06, John Berman wrote: >I have a table called: submissions and each record has an approvedate >field which stores the date mm/dd/yyyy Why? If you're storing the date in this format you can only be storing it as a string (char, varchar or text), so no wonder you're having trouble with it, when MySQL has a perfectly good date storage type in the form yyyy-mm-dd. >I want to display all records for 7 days only from their approved date Assuming you've changed the way you store your dates: SELECT * FROM submissions WHERE DATE_ADD(approvedate, INTERVAL 7 DAY) >= NOW () For what it's worth, the standard American date format of mm/dd/yyyy has always mystified me, as it's the least logical possible way to do it. The SQL format - in decreasing order of unit size - is of course the most logical way because you can guarantee to sort on it and do other calculations. Over here in Europe we at least use dd/mm/yyyy (increasing unit size order), which is the next most logical, but to start with the middle-sized unit, put the smallest unit in the middle and end with the largest is just... weird! -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Marriage has driven more than one man to sex. -- Peter de Vries -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.10.3/395 - Release Date: 21/07/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
