Geek Answers Handbook

Looking between dates in SQL with JDBC?

I am currently writing a Java Swing application that reads data from a MYOB database file and displays specific information in a table. I was able to successfully generate the required SQL queries, but I had problems with the ability to search between dates (our database is quite large, so we are trying to limit the results). An example of one of my queries below (written in Java):

rs = stmt.executeQuery("SELECT sales.InvoiceNumber, sales.ShipToAddress, sales.Date "
                        + "FROM sales, customers "
                        + "WHERE sales.CardRecordID = customers.CardRecordID "
                        + "AND customers.Name = 'Cash Sales' "
                        + "ORDER BY sales.ShipToAddress ASC, sales.Date DESC"
                        + ";");

I have two dates (they are actually strings in Java, but formatted as dd / MM / yyyy).

I tried using a different sentence ANDin mine WHEREwith the operator BETWEEN, but I get the following error from JDBC[MYOB ODBC]Error getting the literal value of right operand.

The actual instruction is given below:

rs = stmt.executeQuery("SELECT sales.InvoiceNumber, sales.ShipToAddress, sales.Date "
                        + "FROM sales, customers "
                        + "WHERE sales.CardRecordID = customers.CardRecordID "
                        + "AND customers.Name = 'Cash Sales' "
                        + "AND sales.Date BETWEEN " + sdate + " AND " + edate + " "
                        + "ORDER BY sales.ShipToAddress ASC, sales.Date DESC"
                        + ";");

- ? .

EDIT: sdate edate 25/10/2013, . , , , . , .

+4
3

:

rs = stmt.executeQuery("SELECT sales.InvoiceNumber, sales.ShipToAddress, sales.Date "
            + "FROM sales, customers "
            + "WHERE sales.CardRecordID = customers.CardRecordID "
            + "AND customers.Name = 'Cash Sales' "
            + "AND sales.Date BETWEEN '" + sdate + "' AND '" + edate + "' "
            + "ORDER BY sales.ShipToAddress ASC, sales.Date DESC"
            + ";");

( , ):

SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
java.util.Date startDate = formatter.parse(sdate);
java.util.Date endDate = formatter.parse(edate);
PreparedStatement pstmt = connection.prepareStatement("SELECT sales.InvoiceNumber, sales.ShipToAddress, sales.Date "
            + "FROM sales, customers "
            + "WHERE sales.CardRecordID = customers.CardRecordID "
            + "AND customers.Name = 'Cash Sales' "
            + "AND sales.Date BETWEEN ? AND ? "
            + "ORDER BY sales.ShipToAddress ASC, sales.Date DESC");
pstmt.setDate(1, new java.sql.Date(startDate.getTime()))
pstmt.setDate(2, new java.sql.Date(endDate.getTime()))

, , , 00: 00: 00.000. , , , . (00: 00: 00.000 ), .

>= " " < " ":

"sales.Date >= '" + sdate + "' AND sales.Date < '" + edatePlusOne + "' "
+9

, :

* , '2006-10-01' '2006-11-30'

, , Google - !:)

0
Class.forName("com.mysql.jdbc.Driver");

connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/hospitall","root","");

PreparedStatement ps=connection.prepareStatement("select * from patient where patientid='"+txtpatientid.getText()+"'");

Statement stm=connection.createStatement();

ResultSet rs=ps.executeQuery();

if(rs.next()){

String patientid=txtpatientid.getText(); 

String str="select * from patient where patientid='"+patientid+"'";

ResultSet result=stm.executeQuery(str);
-1
source

More articles:


All Articles