CODEWITHSUNDEEP
mysql
Assad Nazar
Assad Nazar

Reputation: 1464

MySql get rows from last months same day

I want to get records from last month's same day by keeping in mind for days which will be skipped and should be included in next day.

e.g. on 31st of march, february 31 does not exist so it should skip the query and also if i want to get records on april 30 it will give the results as required, but then on 1st of may, 31st of march will be skipped.

Currently, I am using

SELECT * FROM registrations WHERE orderdate = DATE_SUB(CURDATE(), INTERVAL 1 month)

How can I tackle this in mysql query?

Sorry if i am not able to communicate my query.

Upvotes: 4

Views: 213

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521259

Consider the following logic, which retains a record only if subtracting one month did not result in an earlier day value:

SELECT *
FROM registrations
WHERE
    orderdate = DATE_SUB(CURDATE(), INTERVAL 1 month) AND
    DAY(orderdate) = DAY(DATE_SUB(CURDATE(), INTERVAL 1 month));

Upvotes: 1

Related Questions