Selecting records from the last month

dcjones

Hi all,

I am trying to select all the records in a table that were input last month, example:

if month today is October select all record with month of September

The query I am using is

SELECT payments.*, members.*
FROM payments, members
WHERE payments.coupon_id  = '198C554360' 
AND payments.member_id = members.member_id 
AND payments.begin_date < NOW() - INTERVAL(1 MONTH)

This display an error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'MONTH)' at line 1

I am not sure if my query is written correctly. Can you advise please.

HalfaBee

the query should look like this
AND payments.begin_date > NOW() - INTERVAL(1 MONTH)

dcjones

Hi and many thanks for your reply.

I have adjusted the query to read like this but it still gives an error.

SELECT payments.*, members.*
FROM payments, members
WHERE payments.coupon_id  = '198C554360'
AND payments.member_id = members.member_id
AND payments.begin_date < NOW() - INTERVAL 1 MONTH

Error:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOW() - INTERVAL 1 MONTH' at line 1

HalfaBee

what type of column is payments.begin_date ?

dcjones

Hi.

The payments.begin_date column is a DATE with format YYYY-MM-DD

HalfaBee

AND payments.begin_date > date( NOW() - INTERVAL 1 MONTH )

should work

IAmMike

AND payments.begin_date > DATE_SUB(NOW(), INTERVAL 1 MONTH)

That should also work and maintains timestamp should you need accuracy to that level.

dcjones

Hi,

This is what I have so far but I keep getting an error:

SELECT payments.*, members.*
FROM payments, members
WHERE payments.coupon_id  = '198C554360'  

AND payments.member_id = members.member_id  

AND  payments.begin_date > date( NOW() - INTERVAL 1 MONTH )

The error is
"You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOW() - INTERVAL 1 MONTH' at line 1"