Pete Moran wrote:
Hi All,
Is there a simpler way of doing a select for a given date, for instance if I have a datetime field called date
And so its populated with a load of values such as
2005-01-07 09:00 2005-01-07 10:00 2005-01-07 11:00 2005-01-07 12:00
If I wanted all records which fall on 2005-01-07 I could of course do
Select * from <TABLE> where date > ‘2005-01-07’ and date < DATE_ADD(‘2005-01-07’, INTERVAL 24 HOUR)
However is there a simpler way of doing it by just passing one date like
Select * from <TABLE> where date = ‘2005-01-07’
?
No. A date is fundamentally a range of datetime values, so this is the right way.
I'd like to point out, however, that your query misses midnight (00:00). You should change it to use '>=' instead of '>':
SELECT * FROM <TABLE> WHERE date >= ‘2005-01-07’ AND date < ‘2005-01-07’ + INTERVAL 24 HOUR;
Alternatively, you could add 1 day instead of 24 hours:
SELECT * FROM <TABLE> WHERE date >= ‘2005-01-07’ AND date < ‘2005-01-07’ + INTERVAL 1 DAY;
You could make the query look simpler by changing the datetime column into a date or string and then doing an = comparison, but that would prevent the use of the index.
The only other way to make this simpler would be to split the date and time into separate columns, but that would no doubt cause headaches elsewhere.
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
