Hibernate 3 (EJB3) and oracle date

dondragon2 · **Joined:** Tue Jan 11, 2005 12:58 pm **Posts:** 15

I am have trouble with selecting the maximum timestamp from a oracle date field in a table. I am using oracle database, hibernate 3 annotations and the entitymanager.

The Field ddl is as follows
"UPDTIME" DATE

The class field definition is as follows
@Column(name = "UPDTIME")
private Timestamp updtime;

The follow is the select that I am doing

Code:

Query query = entityManager.createQuery("select o from Transaction o where o.updtime in "
                + "(select max(t.updtime) from Transaction t where rate = ?)");
        query.setParameter(1, rate.trim());
        
        Transaction transaction = (Transaction)query.getSingleResult();

I am trying to find the last transaction with a particular rate. The transactions were initially saved with the system timestamp.

The above sql returns the first transaction always instead of the latest objects because the table doesnt store the timestamp but a date (DD-MMM-YYYY). Is there a way to do a select max(to_char(t.updtime, 'YYYYMMDD HH24:MM:SS') from Transaction t or something similar in order to get the required result?

Thanks

pasha · **Joined:** Sat Nov 27, 2004 4:13 am **Posts:** 137

did you test using @Temporal annotation?

dondragon2 · **Joined:** Tue Jan 11, 2005 12:58 pm **Posts:** 15

Thanks that worked.

Code:

    @Column(name = "UPDTIME")
    @Temporal(TemporalType.TIMESTAMP)
    private Date updtime;