Marcus,

Looking at the date logic in your SQL:

AND MONTH(dbInvoiceDate) = (MONTH(CURDATE()) - 1);

I think the problem you describe is because the MONTH(date) function is returning a numeric value. By the time the right-hand side is evaluated, it is just a number. Then, the left-hand side is evaluated for each row, and also results in a number. The row is a match if both sides are the same number.

So, this is evaluating out to something like this

*(assuming months are 1-based, numbered 1=January, 12=December, so if they're actually 0-based, this will be off by 1)*:

In other words, MONTH() is not giving you

*MONTH and YEAR*, it's just giving you which month of the year, as a number. There are probably similar functions for getting the DAY (day in the month) as a number, and the year, and possibly day of the year, and day of the week (something like 1 for Sunday, 7 for Saturday), etc.

If that logic is correct, you would be getting anything in July....

**any** July, of

**any** year.

Taking the same logic for January, you get:

This is looking for any rows with dbInvoiceDate values of dates,

*in any year*, in the 0th month (which does not exist).

*(Again, this assumes months are numbered starting from 1... if they are 0-based, your expression evaluates out to "-1", which still would not exist.)* So no rows are matched.

What you probably want to do is to either:

1. do actual date comparisons, by using a different SQL function that returns a date value instead of a number, or

2. continue with the numeric comparisons, but you'll have to handle the December/January rollover with some kind of conditional SQL (the condition can happen either in the Java or the SQL), and

you should compare month AND year.

**Example of option #1:** In Oracle, there is a Date function called "TRUNC()", that can "truncate" a date to any particular precision (optional 2nd parameter specifies precision), so the comparison could be something like the following (gives this month's rows):

To do the date arithmetic like subtracting months from CURDATE(), you would use an INTERVAL datatype, which would go something like this:

But then, I kind of suspect you're not using Oracle - I don't know if CURDATE() exists in Oracle, I use "sysdate" for that purpose. But the concepts of working with functions that return dates instead of numbers, and using an interval type in date arithmetic, should be applicable anywhere.

**Example of option #2:** Using your syntax, and assuming there is an analogous function named "YEAR()":

or, in January:

Hope this helps,

Jon