Year Query

[tobeyt23]

I am trying to create yearly invoices for our subscribers. So what I did was create a query to compare todays date to the created date and see if it is a year old. Thought it was working but it pulls everything again on the next day, any suggestions:

 

SELECT invoices.year, users.id, users.user_type_id, users.parent_id, users.username, users.fname, users.lname FROM invoices LEFT JOIN users ON invoices.user_id = users.id WHERE YEAR(invoices.created) <= ".(date('Y')-1)." AND DAYOFMONTH(invoices.created) <= ".date('d')." AND MONTH(invoices.created) <= ".date('m')."

[Barand]

... WHERE invoices.created < CURDATE() - INTERVAL 1 YEAR

[fenway]

Agreed -- in general, let mysql handle dates for you.

[tobeyt23]

Thank you!

[tobeyt23]

This returns no records:

SELECT invoices.year, users.id, users.user_type_id, users.parent_id, users.username, users.fname, users.lname FROM invoices LEFT JOIN users ON invoices.user_id = users.id WHERE invoices.created = CURDATE() - INTERVAL 1 YEAR

 

However this returns what it should why is that:

SELECT invoices.year, 
users.id,
users.user_type_id, 
users.parent_id, 
users.username, 
users.fname, 
users.lname
FROM invoices LEFT JOIN users ON invoices.user_id = users.id
WHERE YEAR(invoices.created) = 2011 AND DAYOFMONTH(invoices.created) = 2 AND MONTH(invoices.created) = 8

[Barand]

The first looks for invoices.created = '2011-08-05'

 

The second looks for invoices.created on August 2 2011.

[tobeyt23]

Sorry I was doing some testing and copied the wrong query. If I break it out for todays date I get results if I do it with CURDATE doesn't return anything.

[Barand]

Does this query give the date you would expect

 

SELECT CURDATE() - INTERVAL 1 YEAR;

 

edit:

 

If your date is a datetime field and not a date field then it will contain a time element so will not be exactly equal.

 

Use

 

...WHERE DATE(invoice.created) = CURDATE() - INTERVAL 1 YEAR

[tobeyt23]

I was just about to ask if it is a datetime field, anyways thanks that worked.