CODEWITHSUNDEEP
javasqloracle-database
Abby D
Abby D

Reputation: 39

Oracle SQL where clause against a timestamp column

I am executing the below SQL query but the query does not return any row even though the entries corresponding to selected date are present in the table. 

    Date getdatetemp =  datechooser.getDate();     // getting the date selected by user
    SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
    java.sql.Date extdate=new java.sql.Date(getdatetemp.getTime());
    String getdate=sdf.format(extdate);
    String sql="SELECT * from registration_timestamp where timestamp_registered=TO_DATE('"+getdate+"','dd/MM/yyyy')";
    PreparedStatement pst=connection.prepareStatement(sql);
    ResultSet res=pst.executeQuery();  
    pst.close();
    res.close();

Upvotes: 0

Views: 2757

Answers (2)

JB Nizet
JB Nizet

Reputation: 691685

Learn about prepared statements, and use native types instead of strings:

java.sql.Date extDate = new java.sql.Date(getDateTemp.getTime());
String sql = "SELECT * from registration_timestamp where timestamp_registered=?";
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setDate(1, extDate);
ResultSet rs = stmt.executeQuery();

That said, since you're talking about timestamps, and timestamps have a precision that goes to the nanosecond, the problem could be that the date stored in the database has a time part in addition to a date part:

16/02/2013 != 16/02/2013 18:54:32 123421

If that's the case (and it seems it is), compare the chosen date with the timestamp truncated to a date:

select * from registration_timestamp where trunc(timestamp_registered) = ?

See http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions201.htm#i79761

Upvotes: 2

Dan Bracuk
Dan Bracuk

Reputation: 20804

When your columns are datetime and you want to search on a date range, you do this:

select whatever
from yourtables
where yourdatefield >= DateA
and yourdatefield < the day after DateB

Combine this logic with the answer from @JBNizet and Bob's your uncle.

Upvotes: 0

Related Questions