aspose file tools*
The moose likes JDBC and the fly likes problem while inserting date field in oracle data base Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "problem while inserting date field in oracle data base" Watch "problem while inserting date field in oracle data base" New topic
Author

problem while inserting date field in oracle data base

Amitosh Mishra
Ranch Hand

Joined: Feb 11, 2010
Posts: 49
Hi all ,

i'm having problem in setting date value in oracle 9 data base.

from my code the value passed to store in database is



but 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.

please provide necessary help

thanks

Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Can you post your code?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19719
    
  20

Moving to our JDBC forum.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
Amitosh Mishra
Ranch Hand

Joined: Feb 11, 2010
Posts: 49
hi paul,

thanks for your quick reply.

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.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

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.
Amitosh Mishra
Ranch Hand

Joined: Feb 11, 2010
Posts: 49
Hi martin,

we are setting date like



Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1134

Amitosh,
You said:

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 ?

Good Luck,
Avi.
Amitosh Mishra
Ranch Hand

Joined: Feb 11, 2010
Posts: 49
Hi avi,

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.
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1134

Amitosh,
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.

Perhaps try using method "setDate(int, java.sql.Date)" instead of "setDate(int, java.sql.Date, Calendar)" ?

Good Luck,
Avi.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Hi Amitosh,

Amitosh Mishra wrote:Hi martin,

we are setting date like




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
Amitosh Mishra
Ranch Hand

Joined: Feb 11, 2010
Posts: 49
Hi Martin,

please find below the information



thanks
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: problem while inserting date field in oracle data base