Searching between 2 date

Question

Have a problem search between 2 date my sql statement in java

String sql ="Select * from Payment where Payment_Date between '"+date_1+"' and '"+date_2+"'";

It give me data type mismatch， I guess my problem occur in '"+date_1+"' and '"+date_2+"' ??

date_1 and date_2 I get from

Date date_1 = date1.getDate();
Date date_2 = date2.getDate();

http://stackoverflow.com/questions/5683728/convert-java-util-date-to-string should fix your issue. — FreudianSlip, Jul 16 '13 at 07:54
what is your Payment_Date data type in database? date? string? — kayla, Jul 16 '13 at 07:58
can you print your 'sql' ? i want to see completed query statement, so i could find what exactly the problem is — kayla, Jul 16 '13 at 08:37

score 2 · Answer 1 · edited May 23 '17 at 12:15

Start using a PreparedStatement , it will prevent SQL injections . Read this SO Q&A for more.

You can do something like this :

String sql ="Select * from Payment where Payment_Date between ? and ? ";
PreparedStatement pstmt = conn.prepareStatement(query);
pstmt.setDate(1, date_1 );
pstmt.setDate(2, date_2 );
// date_1 and date_2 objects should be of type java.sql.Date

Make sure you set the correct parameter types in the setXXX() methods. Remember if the data type for Payment_Date is DATE and related types, you need to set java.sql.Date in the setDate() method. If the data type of column is TIMESTAMP, then use java.sql.Timestamp and setTimestamp() method.

Footnote :-

If you have a java.util.Date object with you , you can convert that to java.sql.Date as :

java.sql.Date sqlDateObject = new java.sql.Date(utilDateObject.getTime());

java.sql.Date date_1 = (java.sql.Date) date1.getDate(); java.sql.Date date_2 = (java.sql.Date) date2.getDate(); Because what I doing is I get the date from JdateChooser and just put it in sql and find the date that I need — Q123Q, Jul 16 '13 at 08:56

score 0 · Answer 2 · answered Jul 16 '13 at 07:57

0

Package of Date class must be java.sql not java.util.

 pstmt = conn.prepareStatement("Select * from Payment where Payment_Date between ? and ?"); 
 pstmt.setDate(1, date_1);
 pstmt.setDate(2, date_2);

answered Jul 16 '13 at 07:57

dijkstra

1,068
2
16
39

Searching between 2 date

2 Answers2

Linked