sorry i can't post the code as it is distributed in many layers.
we are getting the date from UI and converting that date to GMT time then passing the same value to DAO layer .
i have attached the sql log for references.
i tried to to insert time stamp and it inserting +6:30 hours to date.
we are working in GMT+5:30 time zone.
I guess you're using PreparedStatement and are binding the date value. In this case a timezone conversion can occur. Oracle's DATE does not keep timezone information, so the date is stored in as a local date/time in some time zone. There are ways to specify which time zone you want to store the dates in, eg. the PreparedStatement.setTimestamp(int parameterIndex, java.sql.Timestamp x, Calendar cal) method.
The same thing may occur when reading the date from the database and has similar solutions (eg. ResultSet's getTimestamp(int columnIndex, Calendar cal) method).
I assume you're trying to store the date in GMT time zone. In that case you should pass a Calendar instance with the GMT time zone as the last parameter of the above two methods. Actual time in the Calendar instance is not important, just the time zone.
However, it is fairly easy to post example code for this kind of problem. The problem is probably isolated into setting the bind value in the PreparedStatement, which should be just in your DAO layer. You don't need to disentangle all the layers of your application. If you do post an example, somebody who has already solved this might help you. If you don't, you'll have to read javadocs thoroughly and figure it out yourself.
the value which get inserted in database is 12/31/2010 and 3/30/2011
1 day less then what i'm trying to insert.
How did you obtain that value?
Via SQL*Plus ?
Via java ?
Via something else ?
If via java, then the displayed date may not reflect the actual date stored in the database.
Perhaps my article will be of help ?
Joined: Feb 11, 2010
thanks for your reply.
i got this date in toad.
if you see the sql log which i have attached proper date is forwarded in prepared statement. but while inserting in data base it got modified.
The DATE data type in Oracle database actually stores a date and a time.
If you insert a value into a database table column of type DATE that is only a date, as in your example, the time part is set to 00:00:00 (i.e. midnight).
As mentioned in my article (see my previous reply for the link), class Calendar sometimes makes adjustments for daylight savings time.
Hence my guess is that the Calendar class is removing one hour from your date, making it 23:00 on the day before, hence you are seeing 31/12/2010 when you supply 01/01/2011.
In Oracle SQL, use the "to_char" function to display the time component of the value, i.e.
I would definitely suggest to store the new Date( ((java.util.Date)value).getTime() ) expression in a variable and displaying it (or printing it out into a log) immediately before passing it into the setDate. It might not be the problem, but if I had to solve it, I'd want see the exact representation of what I'm passing to JDBC. You know, new Date() will probably introduce your local time zone into the newly created date.
Secondly, please post the following information:
the date you're saving (local time zone representation),
the date you're saving - the value variable from your statement,
the date you're saving - result of the new Date() expression from your statement,
value you expect in the database,
value in the database you actually see via eg. sqlplus select to_char(...) with hours, minutes and seconds