How to get sysdate, time and timestamp from database using sleep mode

Question

In my application, I need to get the database date ( sysdate in case of Oracle DB) and compare it with the user input date ( String converted to java.util.Date ). From this forum I got the following code that helps in the Oracle dialog.

 public Date getDate() { Session session = getHibernateTemplate().getSessionFactory().openSession(); SQLQuery query = session.createSQLQuery("select sysdate as mydate from dual"); query.addScalar("mydate", Hibernate.TIMESTAMP); return (Date) query.uniqueResult(); } 

And from this link I got the following method, which uses the formula mapping file.

 <property name="currentDate" formula="(select sysdate from dual)"/> 

Again, this is typical of Oracle. I think using the later method is more performance friendly, because we can get it from the same session, i.e. No need to open another session just to get the date.

I am looking for a universal solution to get the date, time and timestamp from any DBMS using Hibernate. Using HQL is preferred. We hope that such a solution is available.

Answer 1

For those looking for a .NET / C # solution, here is what worked for me:

 // this works only with Oracle public DateTime DbTimeStamp(ISession session) { // Sample returned value = "12-OCT-11 01.05.54.365134000 AM -07:00" string sql = "SELECT SYSTIMESTAMP FROM DUAL"; ISQLQuery query = session.CreateSQLQuery(sql) .AddScalar("SYSTIMESTAMP", NHibernate.NHibernateUtil.DateTime); return query.UniqueResult<DateTime>(); }