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