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

use between?

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

Answer 2

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 3

Depending on data and business rules, the two queries will produce different results.

The first query, which can be re-written to use BETWEEN (for legibility, no performance change), is guaranteed to return rows whose date column value is between July 20th at 00:00:00 and July 24th at 00:00:00.

The second query will return records whose date column value is greater or equal to July 20th, 00:00:00. If there are dates in the future, this query will return those as well. Because a default constraint will only set the date column if no value is provided - you need a check constraint (not supported by MySQL on any engine though syntax exists) or trigger to ensure that data matches your business rules.

Conclusion

Being explicit is best - easier to understand, which makes it easier to maintain.

Answer 4

Your examples don't produce the same result!

The first one would be better with BETWEEN - it searches between two dates

The second one just searches for entries with DATE => 2010-07-20

These are totally differen operations!