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
Joined: Feb 16, 2011
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?
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.
G Era designator
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
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.
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.