aspose file tools*
The moose likes JDBC and the fly likes SQL Show Previous Month Date Question Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL Show Previous Month Date Question" Watch "SQL Show Previous Month Date Question" New topic
Author

SQL Show Previous Month Date Question

Marcus Hathaway
Ranch Hand

Joined: Jun 07, 2005
Posts: 89
Hi all, can anybody offer any advice on how to show a previous month's date. I want to be able to select invoices for the current month, last month, 2 months ago etc....So if i wanted to view last month's invoices, all the invoices for July would show. I'm using mysql and current trying sql commands such as:

SELECT * FROM Invoice WHERE MONTH (dbInvoiceDate) = MONTH(CURDATE());

This is working great for shoing invoices of the current month, but i'm having trouble figuring a way to show last month. I thought adjusting the code to MONTH(CURDATE() - 1) may work but it doesn't....

I'm thinking i may need to use SUBDATE. I tried various configurations and can show 1 month back from the day (so if did today would have dates 4/7-4/8). Any suggestions?! I'm quite new to sql and sure this is something embarrassingly simple! Cheers
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30548
    
152

Marcus,
If you are using JDBC, you can use a PreparedStatement like:
SELECT * FROM Invoice WHERE MONTH (dbInvoiceDate) = MONTH(?);

Then you can pass in a Java Date for the ?. In Java it is easy enough to add or subtract one month from the date.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Marcus Hathaway
Ranch Hand

Joined: Jun 07, 2005
Posts: 89
Thanks Jeanne for your reply. Yes, that would be a useful way of doing it! However, having only just read your reply i managed to do it using the sql code like so:

SELECT SUM(dbInvoiceTotal)
FROM invoice, acccustomerinvoice
WHERE invoice.dbinvoiceid = acccustomerinvoice.dbinvoiceid
AND dbacccustomerid = (passed in from java)
AND MONTH(dbInvoiceDate) = (MONTH(CURDATE()) - 1);

However, i've found a potential problem with this. The sql code above returns the invoice totals for the selected customer 1 month previous. So if i did it today in August i would see the totals for all the invoices in July. HOWEVER, if i run the code in January i see no totals for December. Does anybody know why not!? The way i spotted this was when trying to run the code which returned outstanding invoices for over 3 months ago. The key date line i am using is:

AND MONTH(dbInvoiceDate) <= (MONTH(CURDATE()) - 3)

It returns all invoives over 3 months old except ones done in 2004 or before. I can't understand why it can't go back to December Agh!

Any tips, suggestions would be greatly appreciated!
Alex Prawira
Greenhorn

Joined: Mar 05, 2005
Posts: 16
" In Java it is easy enough to add or subtract one month from the date. "

How to do that ? can you give me some code example. Thanks for the help.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30548
    
152

Marcus,
What database are you using?

Alex,
You can do the following:
Jon Egan
Ranch Hand

Joined: Mar 24, 2004
Posts: 83
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
Marcus Hathaway
Ranch Hand

Joined: Jun 07, 2005
Posts: 89
Hi Jon,

Thanks for the detailed advice. Yes, your right of course, it was just subtracting numbers......so if i searched for the previous month it showed up July fine. However, it shows up July for 2004, 2003 and any other year with invoices in July!!

Because i'm using MySql the code which i have used to sort out the problem includes date lines as follows:

For example, if i want to search the previous months invoices:

AND DATE_FORMAT(dbinvoicedate, '%M%Y') = DATE_FORMAT(SUBDATE(CURDATE(), INTERVAL 1 MONTH), '%M%Y')

Thanks for all the advice, you guys pointed me in the right direction!
 
Don't get me started about those stupid light bulbs.
 
subject: SQL Show Previous Month Date Question