Jump to content

Year Query


tobeyt23

Recommended Posts

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')."

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.