How to get current date?

flindet · **Joined:** Thu Mar 17, 2005 1:03 pm **Posts:** 4

Hi, I'm embarrassed to have to ask this, but can someone tell me how to query for the current date?

I'm able to do this with a Hibernate Session using just straight JDBC code, but it's my understanding that not all databases have a "dual" table. So, my query of "select sysdate from dual" may not be portable.

I've searched Google for hours, and the FAQs, but I must be using bad search strings because I'm not finding this! I see lots of Hibernate code out there (including in the Hibernate documentation) that compares dates against "current date", so I'm thinking there's a way to get "current date" in a nice portable way using Hibernate.

Can anyone help me rewrite this in a happy, portable way? Thank you!

Hibernate version: 2.1.8

Code between sessionFactory.openSession() and session.close():

Code:

Session session = HibernateContext.getSession("java:/hibernate/SessionFactory");
                Connection connection = session.connection();
                Statement statement = connection.createStatement();
                ResultSet rs = statement.executeQuery("select sysdate from dual");
                rs.next();
                this.timestamp = rs.getTimestamp(FIRST_COLUMN);
                rs.close();
                session.close();

Name and version of the database you are using:Oracle 9i 9.2.0.3

puffybsd · **Joined:** Mon Sep 29, 2003 9:39 am **Posts:** 67

Why query for it? Why not create a java.util.Date object to get the current date.

Code:

new Date();

creates a current date.

bcanard · **Joined:** Thu Mar 17, 2005 6:17 pm **Posts:** 1

I want to know the date at the database level, not the jvm level. Doing new Date() gives me the date at the jvm level, which in my case is a different machine than where the database is.

puffybsd · **Joined:** Mon Sep 29, 2003 9:39 am **Posts:** 67

current_date is a SQL-92 function, which should give you a standard if not portable way of getting the date through the database.

hibernfan · **Joined:** Thu Mar 17, 2005 8:44 pm **Posts:** 6

Actually hibernate doesn't support calling database-specific SQL functions from the select clause for all Hibernate SQL Dialects. Although same sql functions might work in the where clause.
But i think it does support that for Oracle...

try something like this from your HQL :

select sysdate from DataBean db

Here DataBean is your persistent class and db is alias.

I hope that will work if you are lucky enough. :). I don't know why gavin or hibernate team didn't mention in the hibernate documentation that which sql functions from which database are supported in the select clause.

jcurry · **Posted:** Fri Mar 18, 2005 2:27 pm

If all that's keeping you from doing what you want is the absence of a table called dual why don't you just plan on creating one in any database that doesn't have one? Or if you don't like using dual just have another application table called MY_TABLE_THAT_I_USE_TO_GET_THE_DATE_FROM. (you get the idea)

puffybsd · **Joined:** Mon Sep 29, 2003 9:39 am **Posts:** 67

What a let-down!

In addition to creating a dual table where they do not exist, you could
also make a view that has the same name in all your dbs.
In ms sql server:
CREATE VIEW my_time
AS
SELECT CURRENT_TIMESTAMP AS my_date

You can even map it in hibernate...