View Full Version : <= less than or equal, datetime in mysql statement??

09-16-2006, 05:52 PM
Hi, I'm stuck on what I think is a quirk of MySQL.

I have a table 'tblTest'
I have a field in their called 'timestamp' and it is the type 'datetime'. Typical value might be: "2006-05-16 10:46:23"

I have this SQL query to select orders between certain dates:

FROM `tblTest`
WHERE timestamp >= '2006/05/01' AND timestamp <= '2006/05/31';

This selects records with a timestamp of 2006-05-01 through to 2006-05-30.

My thinking is that records for 2006-05-31 should also be returned and yet they are not!!! Why is that?

If I adjust the last date to be 2006/06/01 in includes records on 2006-05-31 but not 2006-06-01. So I guess the equals part of <= isn't working int eh MySQL query and I need to work out why and how to achieve the result I need.

Can anyone explain this to me please? (PS it makes no difference if I change the / for -)

Yours hopefully


09-16-2006, 06:54 PM
First don't use reseverd words like timestamp as col names ;)

and check the between statement from the mysql manual

09-16-2006, 07:12 PM
if you don't give a full timestamp (i.e. yours are missing a time) then it assumes 00:00:00, i.e the very beginning of the day, so >= will appear to be inclusive, while <= won't

09-16-2006, 07:37 PM
Hi Olaf
I had no idea timestamp was a reserved word. Can you point me at a list of reserved words please? Thanks for pointing that out. I had tried BETWEEN beforehand as well. Same problem.

Hi GJay
Thanks that was it. I was overlooking the default time values it was working with. Code now corrected. Thanks, that was very helpful.

Cheers all.

09-16-2006, 08:57 PM
Mysql reserved words (http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html). You will note an odd thing in that timestamp isn't on the reserved word list, but it kind of is. MySQL has decided to allow some words to be used because they had previously been used. Timestamp is one of those. but out of habit, since it is a column type you shouldn't also use it as a column name.

09-17-2006, 02:46 PM
Thanks, that makes a lot of sense, it just hadn't occured to me before. thanks for the help everyone.