Between Syntax

[manisha ankolekar]

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]

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]

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]

String sql="Select nofrom tble where aDate>= ? and aDate<=?"; 

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.