Selecting all entries in a table before this month

I’ve been trying different solutions but none seem to return the same values as I get from excel.

I want to return all the items from a table where the entries in a column are before the current month value. So, if this month is January 2020, return the values before January 2020.

Ideally it’s going in a function where I am passing in the number of months back

SELECT *
FROM tbl_name
WHERE date_column < (NOW() - INTERVAL 1 MONTH).

I’ve tried so many variations, I’ve now confused myself! Lots of the times it’s returning NULL, and this variation returns a value, but it’s missing some values.

Answer

To get all values before the start of the current month, you can do:

SELECT * 
FROM tbl_name 
WHERE date_column <  CONCAT(DATE_FORMAT(CURRENT_DATE, '%Y-%m'), '-01')

You can then shift the boundary with date arithmetic: for example, to get data before the start of the previous month:

SELECT * 
FROM tbl_name 
WHERE date_column <  CONCAT(DATE_FORMAT(CURRENT_DATE, '%Y-%m'), '-01') - INTERVAL 1 MONTH