[SOLVED] MySQL Query

[PatPHP]

Hi Guys

 

I have two fields in the database a StartDate and a EndDate. Now how can I query so that I get all the records for today which are in this range?

 

Greetings

Patrick

[.josh]

SELECT * FROM table WHERE NOW() BETWEEN StartDate AND EndDate

[PatPHP]

Hey thx.... too easy.

[fenway]

SELECT * FROM table WHERE NOW() BETWEEN StartDate AND EndDate

Excep this won't utilize any index on start/end.....

[.josh]

Ha well I readily admit I suck at sql. Can you further explain?

[fenway]

To utilize an index, you need it in the form:

 

SELECT * FROM table WHERE column1 BETWEEN expr1 AND expr2

 

So you have to move it around so one of the columns in being compared against the expressions (which can also contains column references).  Sometimes this means you have to add a 2nd part to the where clause.

 

So

 

SELECT * FROM table WHERE startDate >= NOW() and endDate < NOW()

 

Is "better", though it does obscure the logic a bit.

 

check EXPAIN.

[.josh]

huh.  Well when I originally posted the string I actually wrote it exactly like that, but I thought that that was just a longer way of using BETWEEN. 

[fenway]

huh.  Well when I originally posted the string I actually wrote it exactly like that, but I thought that that was just a longer way of using BETWEEN. 

Not always... it's something the optimizer should do, but doesn't.