Query is not giving desired output

[Saboor880]

hi ! I am developing a pos software for desktop. I am using java derby database for this. I have developed a feature for purchasing items and also a feature for analyzing purchases so that the user can view that what, when, how much he purchased. I am using two dateChoosers one for startDate and other for endDate so that user analyze his purchases according to his desired dates.

I wrote following query:
Select * from purchase where date Between ? And ?

Now i tell you what output i want and what i am getting
For example when i purchase items from my supplier then the date is 20-5-2017. After making this purchase when i go for analyze my purchase, i input startDate 20-5-2017 and endDate 20-5-2017 , i get perfect output i-e all the records which created at 20-5-2017

And when i input startDate 19-5-2017(as there is no purchase record for this date) and endDate 20-5-2017, it gives me perfect output again i-e all the purchase records on 20-5-2017

But when i input startDate 18-5-2017(as there is no purchase record on this date) and endDate 20-5-2107 then it gives me an empty result set. According to me it should again give me records for the date 20-5-2017.

So please guide me what is the reason behind this?

[JamesCherrill]

Wow. That's really weird. What happens for 17-5-2017, or when you change the end date?

[Saboor880]

If i change the startDate to 17-5-2017 then also it gives me empty result set

If i change the endDate to 22-5-2017 then also it gives me empty resultset

[Saboor880]

For checking i creates sample tables in sql server and executed the same query. Here i got perfect results. But i dont know why in java this is happening

[JoelBais]

can you show us the exact sql query?

[Saboor880]

Actually there was no problem in the query. I used java date in the sql instead of sql date. But later i used sql.date for database and my problem solved. Thank u very much for your concern guys.