CODEWITHSUNDEEP
mysqlsqldate
rick
rick

Reputation: 4715

Getting the last month's dates in mysql

I need to get the last month's dates from 1st to current date. Suppose if today's date is March 25th, I need to get the dates from 1st to 25th of february. Suppose if today's date is March 30th, I need to get the dates from 1st to 28/29th Feb, whatever the maximum final date is available. I have searched a lot to get that, but no luck. Can someone please help me how to get this special case done? I am able to do it on another database, but I want to do this on mysql.

Basically what I did for other database is this --> date between date(to_char(date(add_months(DATE(sysdate) ,-1)),'YYYY-MM-01 00:00:00')) and date(add_months(DATE(sysdate) ,-1))

Upvotes: 0

Views: 264

Answers (2)

Ja͢ck
Ja͢ck

Reputation: 173562

This should do what you want:

WHERE d BETWEEN DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH), '%Y-%m-01') 
  AND DATE_SUB(CURDATE(), INTERVAL 1 MONTH);

Upvotes: 2

Omer Atay - MSFT
Omer Atay - MSFT

Reputation: 287

You can use DATE(DATE_SUB(NOW(), INTERVAL 1 MONTH)). This will automatically limit the result to the last day of the month, so if today's date is March 30th, this will return Feb 28th.

Upvotes: 0

Related Questions