How to format dates using hibernate

prajwal · **Joined:** Mon Jul 10, 2006 5:49 am **Posts:** 1

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 3.0

Mapping documents:

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

Full stack trace of any exception that occurs:

Name and version of the database you are using: Oracle 10g

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

[b]Hi All,
We are using hibernate to achieve database independence in our application.Our Application is right now working
fine on mysql database. I am trying to insert date in "yyyy/mm/dd hh:mi:ss" format which is the default date format of mysql.

We are trying to make our application run on Oracle database also.
Right now we are facing problems with date format while querying ,inserting and updating the Oracle database,since the
default date format of Oracle is "dd-mon-yyyy hh:mi:ss". I cannot use oracle specific functions as i have to use the
same code with MySQL also. When i try to insert dates in 'yyyyMMddHHmmss' format, it throws Exception "ORA-01843: not a valid month"

1) Is there any API method in hibernate to handle date format problems?
If so please let me know which is that and how to use it?
2) Is there any way to change the date format of Oracle server itself to a particular format
instead of doing it in each session using NLS_DATE_FORMAT env variable.
3) Is there any utility Class which works fine irrespective of any database.

Query:

select test.pk as pk, test.availability as availabi3_88_, test.created_time as created5_88_
from test_table test
where test.pk>0 and test.created_time>='2006/07/10 00:00:00'
and test.created_time<='2006/07/10 23:59:59' order by test.created_time DESC
[/b]

batmat · **Posted:** Mon Jul 10, 2006 7:40 am

First, maybe you could have verify your post... You'd have seen your post is not well formed at all. That's not a good idea if you want to have more chances to get help...

About your problem, I think you should not specify your dates with any format. You should instead provide a date instance (of java.util.Date for example). Then in fact, Hibernate will be able to convert it into the right format.

bkmr_77 · **Joined:** Tue Apr 25, 2006 12:04 pm **Posts:** 260

If you use JDBC API you should be having no problem w.r.t Dates

Code:

String sql = 
"select test.pk as pk, test.availability as availabi3_88_, test.created_time as created5_88_ 
from test_table test 
where test.pk>? 
and test.created_time >= ? 
and test.created_time <= ? 
order by test.created_time DESC ";

Connection c1 = session.connection();
PreparedStatement ps = c1.prepareStatement( sql );
ps.setInt( 0, 0 );
ps.setDate( 1, getFormattedDate( '2006/07/10 00:00:00' ) );
ps.setDate( 2, getFormattedDate( '2006/07/10 23:59:59' ) );

Where getFormattedDate is an utility function you can implement to get the Date in the required format.

Iff the above code works for your case then you can substitute respective portions with hibernate API also.