How to get dates from date to to date in MS-ACCESS and JSP?

This is a query:

String query1="select * 开发者_高级运维from Demand_Register where Payment_Date> ='"+fromDate+"' and Payment_Date<='"+toDate+"' " ;

if toDate=23/6/2011 and fromDate=25/6/2011 then it retrieves data of dates 24 and 25 only not 23? also if toDate=23/6/2011 and fromDate=23/6/2011 then it retrieves nothing.

Thanks in advance.

Dates in Access/Jet/ACE are stored not as the text representation but as a double, where the integer part is the day since 12/30/1899 and the decimal part is the time. All dates have a time portion.

If you have been using Now() to populate the field in question, or otherwise storing a time part other than 0 in the field, you won't get an exact match. Instead, you'll have to use this:

  toDate>=#23/6/2011# AND toDate<#24/6/2011# AND fromDate>=#25/6/2011# AND fromDate<#26/6/2011#

This will include the toDate values that are for the day 23/6/2011 but have a time portion.

Now, I would question why you are including a time part other than 0 in fields name toDate and fromDate -- if they are truly dates, they should only have the time part. If you're populating those dates in Access or via Jet/ACE SQL, you can use the Date() function, which returns only the date part, instead of Now(), which returns the current date and time.

To fix data that has time values when it should be only date values, just run an update to change the fields to the integer value, e.g., SET toDate = Int(toDate).