Better Way to Handle Dates in Where Clause

Question

I was wondering which type of query is better to use:

SELECT * FROM table WHERE DATE >= '2010-7-20' AND DATE <= '2010-7-24'

OR

SELECT * FROM table WHERE DATE_SUB('2010-07-24',INTERVAL 4 DAY) <= DATE

Answer 1

I always use the >= and < combination.

So if you want to serch for a those four days you would use

where DATE >= '2010-07-20' AND DATE < '2010-07-24'

This ensures that you only get dates your interesrted in even if there's a mixtrure of acutal dates(with no time component ) and datetimes.

So if you had a date stored as 2010-07-20 09:00:00 and a date stored as 2010-07-20 they would both be included, but the date 2010-07-24 wouln't.

I don't trust Between! I'm not sure about MySQL but BETWEEN in SQL Server would give you inclusive results so a row with a date of 2010-07-24 would be included when I wouldn't expect it to be if you wanted the four date period from the 20th? (20th, 21st, 22nd & 23rd)

So ANY date on the 24th shouldn't be included even if it was midnight..!

Also... Typically using functions in your criteria can prevent the optimizer using any indexes..! Something to be aware of.

More Importantly... The second query doesn't produce the same results as the first query...Is it all there?

Answer 2

use between?

SELECT * FROM table WHERE DATE BETWEEN '2010-7-20' AND '2010-7-24'