• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Jeanne Boyarsky
  • Ron McLeod
Sheriffs:
  • Paul Clapham
  • Liutauras Vilda
  • Devaka Cooray
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
Bartenders:

problem while inserting date field in oracle data base

 
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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

 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Can you post your code?
 
Sheriff
Posts: 22821
132
Eclipse IDE Spring Chrome Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Moving to our JDBC forum.
 
Amitosh Mishra
Ranch Hand
Posts: 49
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 49
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi martin,

we are setting date like



 
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 49
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 49
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Martin,

please find below the information



thanks
 
Paper beats rock. Scissors beats tiny ad.
Smokeless wood heat with a rocket mass heater
https://woodheat.net
reply
    Bookmark Topic Watch Topic
  • New Topic