asked on

MySQL Query to extract all dates from previous month

Hi Experts,

Can you please help me with a MySQL query.
My table has a date field and I need a query to extract all rows which date field range is within the
previous month. Whatever the current date is, I need to get all rows from the previous month.

I am obviously using PHP with MySQL so if some code is better to do in PHP please feel free to give me PHP examples.

Thanks!

ASKER CERTIFIED SOLUTION

Raynard7

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Cornelia Yoder

Raynard, does INTERVAL -1 MONTH give you the same date in the previous month? What would happen if NOW was March 31? What date would DATE_ADD(now(), INTERVAL -1 Month) give? Would it be guaranteed to be in Feb?

Raynard7

if it does not work you could replacfe now() with

date_add(now(), interval (-1 * day(now()) + 1)

which would give you the first of the current month, for which one month previous will always be the first of the previous month.

Marius0188

ASKER

Ok, now you two have lost me a little. :)

Let give example:

1. If now is January then I need all rows for the previous month.
In this case it will be December (previous year).

2. If now is February (doesnt matter which day in Feb etc) then I need all rows for
January (same year).

Marius0188

ASKER

Always need the rows for the previous calendar month irrespective of which day it is in the current month.

Thanks.

Cornelia Yoder

Maybe some testing would be in order :)

Cornelia Yoder

Right Marius, we understand your need, just not sure that solution given will actually work.

Raynard7

select * from yourTable where month(yourDateField) = month(DATE_ADD(DATE_ADD(NOW(), interval (-1* day(now) + 1) day, INTERVAL -1 Month)) and year(yourDateField) = year(DATE_ADD(interval (-1* day(now) + 1) day, INTERVAL -1 Month)

definitley works in all circumstances.

Marius0188

ASKER

I think there is some missing brackets. Get's an error.
Though this is such complicated query for me I can't even fix that. :)

Please help.

Cornelia Yoder

I didn't test this, but I think I got the brackets straightened out the way he intended:

select * from yourTable where month(yourDateField) = month(DATE_ADD(DATE_ADD(NOW(), interval (-1* day(now()) + 1) day), INTERVAL -1 Month)) and year(yourDateField) = year(DATE_ADD(interval (-1* day(now()) + 1) day), INTERVAL -1 Month)

Cornelia Yoder

By the way, give the points to Raynard, he's the one who worked out the way to do it :)

Marius0188

ASKER

1. There is some error with the query. Can't use it.

2. BTW: What did you guys mean with that first comment?
In what case might that one not work?
I test it a little and it looks like it can work. But maybe I am not thinking about all the loop holes.

Cornelia Yoder

Raynard was subtracting 1 month from the current date, and I commented that I didn't know what mysql would do with March 31, since there isn't a Feb 31st. I think he modified the query to account for that sort of situation.

Marius0188

ASKER

I have test that in MySQL Front and it work just fine.
I set my pc date to 31 March 2007 and then executed this query:

select domainvisitid,
month(DATE_ADD(now(), INTERVAL -1 Month)) as date from tbldomainvisits

And date = "2".

I guess then it is not a problem?

Cornelia Yoder

Check each month, and especially January. If it works, you're all set :)

Marius0188

ASKER

When pc clock is January then month = "12"
which is perfect.

So I believe it's safe to use?