MySQL Date and Time Operations Testing against a date range
Example
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:
BETWEENis 'inclusive' thereby including the final date or second.23:59:59is clumsy and wrong if you have microsecond resolution on aDATETIME.- This pattern avoid dealing with leap years and other data calculations.
- It works whether
xisDATE,DATETIMEorTIMESTAMP.
