• 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
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

oracle database date format implementation

 
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

In my java application, I have a date string coming from db in different-different formats each time 05-Feb-2013, along with the format pattern DD-MON-YYYY.

I need to convert this date string to java.util.Date. Java's default java.text.DateFormat implementation, which is java.text.SimpleDateFormat will not work here as it can not understand oracle database's format pattern. My question here is

1) Is there any other implementation of java.text.DateFormat like java.text.SimpleDateFormat (which is an implementation of DatePattern after all), which can accept oracle db's format pattern and return java.util.Date?

or

2) Is there any utility to draw java simpledateformat pattern from oracle db format pattern?

or

3) Can it be achieved in any other way?

Thanks in advance
 
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
3) is right

If you're converting the date to a string in your select (using to_char() SLQ function), stop doing that. Instead, you obtain the date directly using ResultSet.getDate or ResutSet.getTimestamp. You don't have to mess with date formats this way. Conversely, when passing dates to the database, you'll use PreparedStatement.setDate or PreparedStatement.setTimestamp. Oracle's JDBC tutorial covers these things nicely.

If you store the dates in the database as VARCHARs, then you obviously need to do the conversion. You might use to_date SQL function in your select statement, which would let you have a text converted to a date by Oracle itself, which lets you use any text format recognized by Oracle, of course. Then read the date using one of the methods mentioned earlier. Look up to_date and to_char functions in Oracle's documentation.

It's not a good idea to store dates as text in the database. It might lead to lots of implicit conversions, and you should always avoid implicit conversions. Besides, it usually makes the queries perform worse than when using DATE for dates.
 
Manohar Reddy Gopireddy
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am not reading a date field through jdbc. In DB it self, it is stored as xml string. This xml string at DB side is generated by converting an oracle type object to xml. during this conversion it is taking that session's default nls_date_format and giving me the string. This format string differs from session to session.

current oracle session's nls_date_format DD-MON-RR. So it is givng the following xml.


I have tried all the possibilities at DB side, all those are neither feasible nor valid(at least in my case).

So, is there a DateFormat provider, that can accept oracle date format strings and return date objects?
 
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
It's quite an unfortunate design.

Your biggest problem is that it depends on the default NLS settings. That's a serious bug. You should remove the dependence on the default NLS format as your first step. In general, it is not possible to write a generic function that will correctly convert text to a date without the knowledge of the date format. It is immediately apparent if you consider formats DD/MM/YYYY and MM/DD/YYYY, both of which are in use somewhere in the world.

If you cannot alter the function that converts the date to a text, you could at least set the NLS date format before calling the function, to achieve predictable conversion to a known format. Decoding known format is simple, in database as well as in Java.
 
Manohar Reddy Gopireddy
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am not asking the question properly it seems.

I will ask a straight forward question now. As far as i know java.text.SimpleDateFormat is after all an implementation of java.text.DateFormat. This implementation of DateFormat can understand the following pattern letters

G Era designator
y Year
M Month in year
w Week in year
W Week in month
D Day in year
d Day in month
F Day of week in month
E Day in week
a Am/pm marker
H Hour in day (0-23)
k Hour in day (1-24)
K Hour in am/pm (0-11)
h Hour in am/pm (1-12)
m Minute in hour
s Second in minute
S Millisecond
z Time zone
Z Time zone



Are there any other implementations of java.text.DateFormat, which can understand some other pattern letter group? If any, please let me know.
 
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
I believe you're addressing the symptoms, not the real cause of the problem. I was trying to offer you solutions I consider better. Code that relies on default NLS is simply wrong, it is a bug. Avoid the date-to-text-to-date conversion, and your problem is solved.

I'm not aware of any other JVM class that would parse dates based on patterns. However, the "dd-MMMM-yy" pattern should work, if you set your locale to US. Note that the two digit year is horrible (the Y2K bug is known for decades), and the necessity of changing Oracle's NLS is clear indication of serious design glitch. Get rid of the conversions, it's much better solution in the long term.

Edit: just to clarify, you can specify the locale when you're creating the SimpleDateFormat.
 
reply
    Bookmark Topic Watch Topic
  • New Topic