[SOLVED] Comparing dates in query with BETWEEN

[markkanning]

Is anyone aware of a problem with MySQL 5.0+ with comparing dates in a query? I've got a query that works fine if I'm comparing dates/times inside of a single day, like this:

SELECT * FROM table WHERE NOW() BETWEEN '2007-09-21 03:00:00' AND '2007-09-21 15:00:00'

But if I compare 2 dates spanning a day, or multiple days, I get nothing.

 

Any thoughts?

 

Mark

[teng84]

Is anyone aware of a problem with MySQL 5.0+ with comparing dates in a query? I've got a query that works fine if I'm comparing dates/times inside of a single day, like this:

SELECT * FROM table WHERE NOW() BETWEEN '2007-09-21 03:00:00' AND '2007-09-21 15:00:00'

But if I compare 2 dates spanning a day, or multiple days, I get nothing.

 

Any thoughts?

 

Mark

be glad you get nothing or else MYSQL means error lol

u use now on your condtion right so its like having a range of 2007-09-21 03:00:00' to 2007-09-21 03:00:00'

i guess what you mean is

SELECT * FROM table WHERE FIELDHERE BETWEEN '2007-09-21 03:00:00' AND now()

 

[fenway]

Is anyone aware of a problem with MySQL 5.0+ with comparing dates in a query? I've got a query that works fine if I'm comparing dates/times inside of a single day, like this:

SELECT * FROM table WHERE NOW() BETWEEN '2007-09-21 03:00:00' AND '2007-09-21 15:00:00'

But if I compare 2 dates spanning a day, or multiple days, I get nothing.

 

Any thoughts?

 

Mark

 

In general, you would try to put your field value outside the "between", so that an index could be used...