Semolas

Hey guys,

I am working on the application to keep track of my finances and do a few other things. I have two tables, Deposits & Withdrawals. Deposits has 3 columns, DATE (an object of type Date -defaults to CURRENT_DATE), WHO_DID (varchar(10) - who made the deposit), and AMOUNT (amount of the deposit - a double). In this program I want to give the user the option to pull up a list of all deposits made in the past 10 days. Off the top of my head I thought it would be something like this:

SELECT * FROM Deposits WHERE DATE >= CURRENT_DATE - 10;

As stuff that I try right off the top of my head is usually wrong, this flopped and said I could not subtract an int from CURRENT_DATE. This wasn't surprising to me. I figured I would do a little digging and find the solution easily. However, I have had trouble finding a solution to this anywhere. It doesn't look like it should be all that difficult to do this. I am using Netbeans that connects to one of their derby databases and using the mysql jdbc to connect to the DB. Any suggestions? Thanks.

joblow11

Hey there,

I found an excellent tutorial that should answer your question. You basically have mismatched datatypes and need to resolve that by adding and subtracting variables of the same type.

Semolas

Thanks for the info. I did learn some things from this tutorial but unfortunately it still didn't have the solution to my problem. I think the problem is that the CURRENT_DATE of course changes daily making it tricky to pull all records in between that date and say 10 days ago. I can't just say find all records between CURRENT_DATE and Apr 14 for example because tomorrow (since I am looking for transactions in the past 10 days) I would need to find transactions between CURRENT_DATE and Apr 15.

ShosMeister

As was mentioned, you need to keep the data types the same. Since you didn't say what flavor of SQL you are using, I'll try to be generic and you can look up the specific syntax in your version.

Do you understand the concept of the data types being the same? In other words, CURRENT_DATE is a date type. The only thing you are allowed to do with that is add or subtract another date type or date part (days, weeks, etc.) - again, this may vary slightly from SQL to SQL.

The basic thing to do is take your "10" and make it into 10 days: CURRENT_DATE - DAY(10). Again though, the exact syntax may vary from SQL to SQL. See if that helps get you going.

Semolas

Sorry for the late reply....my grandfather died earlier this week , but thanks for the suggestions. I tried this statement:

SELECT * from Deposit where DATE > (CURRENT_DATE- DAY(10))

and got an error saying the following:

"The 'EXTRACT DAY' function is not allowed on the 'INTEGER' type."

I am not exactly sure of the 'flavor' of sql I am using. I am using Netbeans with the built in Apache Derby that uses this driver:

org.apache.derby.jdbc.ClientDriver

If any of that info helps. Thanks again for the suggestions.

ShosMeister

Understand NetBeans, use it myself, but still not sure what database you are using. It probably doesn't matter too much though as the error is telling you one of the variables you are using is an integer. Verify DATE is in fact a date type in the table (and it's probably not a good idea to use a reserved word for a field name) and that CURRENT_DATE (I'm guessing is a variable in the code you are writing whatever language that is) is a date object. Last is to check the syntax for how what you are doing in NetBeans allows for adding values to dates as I've not used it for any database connections, just for some JAVA work for school.

Post the code you have around that specifically where you setup CURRENT_DATE. That may help.

Hope your family is doing well with the loss. It's never easy, but, all good things as they say.

Semolas

Sorry again for the late reply.........I don't really setup the CURRENT_DATE variable. In my table DEPOSITS, I have all entries default to the current date. So, when I am setting options/data types for this table, under the default option for the date of the deposit, I place CURRENT_DATE. Which looks to be a built in variable of the sql I am using (at least thats how I understand it). This appears to work, as for each deposit I enter into my system the date at the time has been placed in the date field.

Here are some pieces of my code that might help....the order of the code is the order of how it is executed.

//Import statements involving sql only
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.DriverManager;
//end import statements

//setting up database location, driver name, username, and password
private final String DB_URL = "jdbc:derby://localhost:1527/spending";
private final String DRIVER_CLASSNAME = "com.mysql.jdbc.Driver";
private final String USER_NAME = "thill";
private final String USER_PASSWORD = "kristen";
/**
* Connection to archive location database
*/
Connection db_connection;
/**
* JDBC object to run SQL queries
*/
Statement st;
/**
* Status message;
*/
String status;
/**
* List of messages matching a particular receiver
*/
Vector messages;
//end db variable setup
.
.
.
//button gets clicked to go in and 'grab' the deposits from last 10 (or x days - just have been using 10 as a round number to test) NOTE: pullDepositsFromXDays gets called
private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {
if(jComboBox7.getSelectedItem().equals("List Deposits From Last 10 Days")){
String theresults = this.pullDepositsFromXDays(0);
theresults = "Deposits Made In Last 10 Days: \n" + theresults;
jTextArea1.setText(theresults);
}
//end of this segment
.
.
.
//Called by previous code segment - connects to DB and attempts to go in to retrieve deposits from last 10 days NOTE: this works when I select all deposits instead of just the ones from last 10 days.

public String pullDepositsFromXDays(int x){
String qresultsstring = "";

Vector qresults = null;

double total = 0;
try{
String results = "SELECT * from Deposit where DATE > (CURRENT_DATE- 10)";

db_connection = DriverManager.getConnection(DB_URL,USER_NAME, USER_PASSWORD );
st = db_connection.createStatement();
ResultSet rs = st.executeQuery(results);
qresults = new Vector();
while(rs.next()){
double nexttotal;
qresults.addElement(rs.getDate("DATE"));
qresults.addElement(nexttotal = rs.getDouble("AMOUNT"));
total = total + nexttotal;
qresults.addElement(rs.getString("WHO_SPENT"));
}//end while
qresults.trimToSize();
if(db_connection != null)
db_connection.close();
if(st != null)
st.close();
if(rs != null)
rs.close();

}
catch(Exception e) {
e.printStackTrace();
System.exit(0);
}//end catch
qresultsstring = formatDepositVector(qresults,total);
return qresultsstring;
}

Thanks again for any help.

ShosMeister

I don't have any SQL stuff loaded to test so I'm just going from memory. If the CURRENT_DATE is in fact a SQL global, then the first example I gave should work. If it's not, then you'll need to find the equivalent SQL function (something like Getdate() or Now()).

Also, see if there is a dateadd function and try it with a negative parameter: Dateadd(-10, Getdate()).

Semolas

Well, after looking into the Dateadd function I had high hopes for it, but once again I got an error...'DATEADD' is not recognized as a function or procedure.

Maybe I will try to do this outside of Netbeans....it seems as though these suggestions should be working or at least not the kind of errors I am getting.