On 24 Feb 15:13, James Bensley wrote:

SELECT blah, blah, blah FROM tblBlah WHERE date_entered BETWEEN '01/01/2010' AND '20/01/2010'

This will select records from my table who's date_entered field is from 01/01/2010 and 19/01/2010. This is because the BETWEEN command seems to go UP TO the day specified but not inclusive.

So I thought I would just add +1 on to the data submitted from the PHP search page which works just great except for when you put 31 in as the day of the month, (so say we search from 01/01/2010 to 31/01/2010) it makes it into 32 and the querie returns 0 rows. However if I add an IF statement to not increment the day value by 1 if "31" is given as the last day in the query, we then only get results for 01/01/2010 to 30/01/2010.

How can I combat this or is there a "proper" way of doing this as mine almost defiantly isn't going to be the best method?

Are they pure dates? Cos you could probably use: SELECT blah, blah2, blah3 FROM tbl_blah WHERE date_entered >= '01/01/2010' AND date_entered <= '20/01/2010'; That's untested... but probably does what you're expecting. Cheers, -- Brett Parker

James Bensley wrote:

SELECT blah, blah, blah FROM tblBlah WHERE date_entered BETWEEN '01/01/2010' AND '20/01/2010'

This will select records from my table who's date_entered field is from 01/01/2010 and 19/01/2010. This is because the BETWEEN command seems to go UP TO the day specified but not inclusive.

So I thought I would just add +1 on to the data submitted from the PHP search page which works just great except for when you put 31 in as the day of the month, (so say we search from 01/01/2010 to 31/01/2010) it makes it into 32 and the querie returns 0 rows. However if I add an IF statement to not increment the day value by 1 if "31" is given as the last day in the query, we then only get results for 01/01/2010 to 30/01/2010.

A quick look at the docs suggests that you could try SELECT blah, blah, blah FROM tblBlah WHERE date_entered BETWEEN '2010-01-01' AND ADDDATE('2010-01-19', 1) - in this form, adddate assumes days as the second argument. Have a scope at http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function... Simon -- ====================================================================== Simon Ransome http://nosher.net Photo RSS Feed: http://nosher.net/images/images.rss

On Wed, February 24, 2010 3:13 pm, James Bensley wrote:

How can I combat this or is there a "proper" way of doing this as mine almost defiantly isn't going to be the best method?

Were this postgres.... postgres=# select '2009-02-28'::date+'1 day'::interval; ?column? --------------------- 2009-03-01 00:00:00 (1 row) Perhaps mysql has a similar function? Mart. -- http://antibodyMX.net - antispam and antivirus email filtering.