MySQL Selecting Dynamic Date Range

Question

I have a datetime column and I want to select all rows from the previous 2 complete months (Sept and Oct, currently). Manually I could compose this query as:

where date_column between '2018-09-01' and '2018-10-31'

but how can I do this programmatically so the end date is always correct? I was thinking

where date_column between concat(substr(now() - INTERVAL 2 month, 1, 7), '-01') and concat(substr(now() - INTERVAL 1 month, 1, 7), '-31')

but the 31 will be incorrect for November, February, etc.

Answer 1

You could format the date and just hard code the day to be 1:

date_column BETWEEN
            DATE_FORMAT(NOW() - INTERVAL 2 MONTH, '%Y-%m-01') AND
            DATE_FORMAT(NOW(), '%Y-%m-01')