Example
SELECT ... WHERE dt >= '2017-02-01'
AND dt < '2017-02-01' + INTERVAL 1 MONTH
Sure, this could be done with BETWEEN and inclusion of 23:59:59. But, the pattern has this benefits:
- You don't have pre-calculate the end date (which is often an exact length from the start)
- You don't include both endpoints (as
BETWEENdoes), nor type '23:59:59' to avoid it. - It works for
DATE,TIMESTAMP,DATETIME, and even the microsecond-includedDATETIME(6). - It takes care of leap days, end of year, etc.
- It is index-friendly (so is
BETWEEN).
