Testing against a date range

Although it is very tempting to use BETWEEN … AND … for a date range, it is problematical. Instead, this pattern avoids most problems:

WHERE x >= '2016-02-25'
  AND x  < '2016-02-25' + INTERVAL 5 DAY

Advantages:

• BETWEEN is ‘inclusive’ thereby including the final date or second.
• 23:59:59 is clumsy and wrong if you have microsecond resolution on a DATETIME.
• This pattern avoid dealing with leap years and other data calculations.
• It works whether x is DATE, DATETIME or TIMESTAMP.