[Oracle 9i] [Hibernate 3.0] sysdate.

JackyC · **Joined:** Thu Apr 14, 2005 9:02 am **Posts:** 7

Hibernate 3.0.
Oracle 9i.

In the config file, both the "Oracle" dialect and the "Oracle 9i" dialect are chosen.

The following Oracle sql statement returns the current date and time of the database server:

Code:

select sysdate from dual;

Now try to use HQL to get the same result:

Code:

            String hql = "select sysdate() from Category";  // "sysdate()" is correct, but "sysdate" not.
            Query q = session.createQuery(hql);
            List result = q.list();
            
            Date sysdate = (Date) result.get(0); 
            System.out.println("sysdate = " + sysdate.toLocaleString());

Output: The time component of the output is always 00:00:00.

Now try to use a SQL query:

Code:

            String sql = "select sysdate from dual";
            SQLQuery sq = session.createSQLQuery(sql);
            Object[] x = (Object[]) sq.uniqueResult();
            
            System.out.println(x.length);

Output: 0.

Please explain those output.

max · **Posted:** Sun Apr 24, 2005 7:57 am

JackyC wrote:

[
In the config file, both the "Oracle" dialect and the "Oracle 9i" dialect are chosen.

i hope you meant tried and not chosen as in both dialect chosen at the same time ?

Quote:

Code:

            String hql = "select sysdate() from Category";  // "sysdate()" is correct, but "sysdate" not.
            Query q = session.createQuery(hql);
            List result = q.list();
            
            Date sysdate = (Date) result.get(0); 
            System.out.println("sysdate = " + sysdate.toLocaleString());

[size=18]Output: The time component of the output is always 00:00:00.
[/code]

Yes, sysdate() is a HQL function that returns a DATE not a TIMESTAMP.
Use systimestamp() ot get the wanted behavior.

Quote:

Code:

            String sql = "select sysdate from dual";
            SQLQuery sq = session.createSQLQuery(sql);
            Object[] x = (Object[]) sq.uniqueResult();
            
            System.out.println(x.length);

[size=18]Output: 0.

you haven't specfied what the query returns thus hibernate just return one row with zero columns (we should maybe throw an exception here),
so to get what you want do the following:

Query sq = session.createSQLQuery(sql).addScalar("sysdate", Hibernate.TIMESTAMP);

Quote:

Please explain those output.

Please read the manual and check the log output in the future :)

/max

JackyC · **Joined:** Thu Apr 14, 2005 9:02 am **Posts:** 7

Code:

            String sql = "select sysdate as system_datetime from dual";
            SQLQuery sq = session.createSQLQuery(sql);
            sq.addScalar("system_datetime", Hibernate.TIMESTAMP);

            Timestamp sysdatetime = (Timestamp) sq.uniqueResult();

And, I used both Oracle and Oracle9i dialect at the same time.
I think it can choose one smartly.

max · **Posted:** Sun Apr 24, 2005 4:55 pm

well submit a patch if you want that ,)

ziesemer · **Posted:** Thu Mar 20, 2008 4:10 pm

I proposed a fix for this at http://opensource.atlassian.com/projects/hibernate/browse/HHH-3193.

redysanin · **Joined:** Sat May 08, 2010 9:00 am **Posts:** 1

Hi
you can use type as Timestamp
and Statement stmt= session.connection().createStatement();
Resultset rs =stmt.excutequery("select sysdate from dual");
if(rs.next()){
rs.getTimestamp(1);
}
then insert this timestamp it will work.