• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Between Syntax

 
manisha ankolekar
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Please Help me with the code to find records between start date and end Date provided from ComboBox.

The date column in database is of Date/Time itself.I'm using MS Access db.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Manisha,
Have you tried writing the SQL query yet? If so, post what you have so far? If not, what part are you stuck on? What did you try that didn't work. Posting this shows you put some thought into it and aren't just trying to get someone to do a project for you.
 
manisha ankolekar
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The code I tried out:



Date d ;
Date d1;
DateFormat formatter,formatter1 ;
String s=cbDate.getSelectedItem()+"-"cb.getSelectedItem()"-"+cbYear.getSelectedItem();

String e=cbDate1.getSelectedItem()+"-"cb1.getSelectedItem()"-"+cbYear1.getSelectedItem();
try
{
formatter= new SimpleDateFormat("dd-MMM-yy");

d = formatter.parse(s);

System.out.println("d "+d);

d1 = formatter.parse(e);

System.out.println("d1 "+d1);
String sql="Select nofrom tble where aDate>= ? and aDate<=?";
pstmt=cn.prepareStatement(sql);
pstmt.setDate(1,new java.sql.Date(d.getTime()));
pstmt.setDate(2,new java.sql.Date(d1.getTime()));
rs=pstmt.executeQuery();
while(rsOrders.next()){
System.out.println(rsOrders.getString("no"))}
}

catch(Exception ex){}



No results
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Thanks for showing the code. This query is really close and would work if the column were of type date.

Since the column is of type date and time, you need one more step. If you were to print out the dates you are passing into the prepared statement, you would see they have time values set to something. The database is including these times in the "between" search - something you do not want.

A common solution is to write a function that sets the first date's time to "0" (beginning of day) and the second date's time to the end of the day. This will cause the SQL to include the whole day you are testing for.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic