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?
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,
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.
is not INTERVAL the way to add a date, so it rolls over the month correctly
DATE_ADD('31/01/2010', 1 day) or '31/01/2010' + INTERVAL 1 day
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
On 24 February 2010 15:13, James Bensley jwbensley@gmail.com 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.
...
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?
Well, in addition to the other solution mention, the correct way to add to dates is typically database dependent. For example, you could do in MySQL ...
SELECT blah, blah, blah FROM tblBlah WHERE date_entered BETWEEN '01/01/2010' AND DATEADD('d', 1, '20/01/2010')
which would get around your "32 day in month" problem.
Greg
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.
On 24/02/10 15:41, Martin A. Brooks wrote:
postgres=# select '2009-02-28'::date+'1 day'::interval; ?column?
2009-03-01 00:00:00 (1 row)
Perhaps mysql has a similar function?
SELECT '2009-02-28' + INTERVAL 1 DAY;
Or, for James: SELECT blah, blah, blah FROM tblBlah WHERE date_entered BETWEEN '2010-01-01' AND '2010-01-28' + INTERVAL 1 DAY
Note the date format; when I tried it, MySQL didn't do a great job of interpretting '28/01/2010'.
On 24 Feb 16:06, Mark Rogers wrote:
On 24/02/10 15:41, Martin A. Brooks wrote:
postgres=# select '2009-02-28'::date+'1 day'::interval; ?column?
2009-03-01 00:00:00 (1 row)
Perhaps mysql has a similar function?
SELECT '2009-02-28' + INTERVAL 1 DAY;
Or, for James: SELECT blah, blah, blah FROM tblBlah WHERE date_entered BETWEEN '2010-01-01' AND '2010-01-28' + INTERVAL 1 DAY
Note the date format; when I tried it, MySQL didn't do a great job of interpretting '28/01/2010'.
I seem to remember that it's locale dependant... But I don't tend to use MySQL much these days anyways... ;)
On 24/02/10 17:50, Brett Parker wrote:
I seem to remember that it's locale dependant...
Could well be. I learned long ago that the only reliable date format is %Y-%m-%d
But I don't tend to use MySQL much these days anyways... ;)
I've been planning a migration to PostgreSQL for years but never got around to it.
On 24 February 2010 16:06, Mark Rogers mark@quarella.co.uk wrote:
SELECT blah, blah, blah FROM tblBlah WHERE date_entered BETWEEN '2010-01-01' AND '2010-01-28' + INTERVAL 1 DAY
Sorry guys it has been a while since I've had a chance to revisit this. Just to say for anyone else's reference this was the ticket for me. Thanks to all for the help!
-
Brett Parker -
Greg Thomas -
James Bensley -
James Taylor -
Mark Rogers -
Martin A. Brooks -
Simon Ransome