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
BETWEEN does), nor type ‘23:59:59’ to avoid it.
- It works for
DATE, TIMESTAMP, DATETIME, and even the microsecond-included DATETIME(6).
- It takes care of leap days, end of year, etc.
- It is index-friendly (so is
BETWEEN).
Found a mistake? Have a question or improvement idea?
Let me know.