aspose file tools*
The moose likes Oracle/OAS and the fly likes oracle database date format implementation Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "oracle database date format implementation" Watch "oracle database date format implementation" New topic
Author

oracle database date format implementation

Manohar Reddy Gopireddy
Greenhorn

Joined: Feb 16, 2011
Posts: 10
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
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3456
    
  47

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

Joined: Feb 16, 2011
Posts: 10
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 Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3456
    
  47

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

Joined: Feb 16, 2011
Posts: 10
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 Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3456
    
  47

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.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: oracle database date format implementation
 
Similar Threads
Date format
Formatting Dates
How to check if the given date is valid?using SimpleDateFormat.Is this short and rig
Date format in JSP
Need to change Calendar to dd/mm/yyyy format