Member Avatar for 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?

  • 3 Contributors
  • 5 Replies
  • 241 Views
  • 3 Weeks Discussion Span
  • Latest Post Latest Post by Saboor880

Dani AI

Generated

Quick summary: the problem was not the SQL but the Java type bound to the parameters. discovered that the date chooser returned a java.util.Date and the PreparedStatement parameters were not using java.sql.Date; after switching to java.sql.Date the BETWEEN behavior returned to normal. Different JDBC drivers handle a java.util.Date/setObject differently, which also explains why tests against SQL Server gave different results.

Key points and a safe pattern to follow:

  • Match the JDBC setter to the SQL column type: use java.sql.Date for DATE, java.sql.Timestamp for TIMESTAMP.
  • Convert a java.util.Date from a date picker before binding:
java.util.Date uiStart = startChooser.getDate();
java.util.Date uiEnd   = endChooser.getDate();
java.sql.Date startSql = new java.sql.Date(uiStart.getTime());
java.sql.Date endSql   = new java.sql.Date(uiEnd.getTime());
ps.setDate(1, startSql);
ps.setDate(2, endSql);
  • If the column is TIMESTAMP and you want the entire end day included, either set the end parameter to the end-of-day timestamp or write the predicate as date >= :start AND date < :endPlusOneDay to avoid millisecond/timezone edge cases.
  • When debugging, print the parameter values and getTime() (milliseconds) to confirm what the driver sends. Also verify the Derby column type (DATE vs TIMESTAMP) and remember drivers differ in how they coerce types.

This ties back to ’s probing about changing dates and ’s request for the SQL — the surprising empty result came from a type mismatch rather than the BETWEEN logic. Matching parameter types explicitly (or using java.time.LocalDate with JDBC 4.2+) prevents this class of bug.

Recommended Answers

All 5 Replies

Member Avatar for JamesCherrill

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

Member Avatar for 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

Member Avatar for 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

Member Avatar for JoelBais

can you show us the exact sql query?

Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.