File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Reading date from Oracle based on calendar Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Reading date from Oracle based on calendar" Watch "Reading date from Oracle based on calendar" New topic
Author

Reading date from Oracle based on calendar

Naveen Kumar
Ranch Hand

Joined: May 29, 2002
Posts: 30
HI,

I want to read the date from Oracle as stored in Oracle.

From settings of Oracle the data format & calender are as follows.

NLS_CALENDAR=PERSIAN
NLS_DATE_FORMAT=DD MONTH YYYY

On my OS side i have also set the same parameters. (When my JVM is running)
Now when i connect to oracle using sqlplus and run the following query,
"select sysdate from dual"
the result is
SYSDATE
------------------
25 Dei 1383

But then I use JDBC java program and run the same query i get the following.
------------------
2005-01-14

These two results are based on two different calendars. The first result in sqlplus is based on PERSIAN Calender, but the result using JDBC is based on GREGORIAN.

Can any one suggest me the setting that I need to use in JDBC, so that I get the date from oracle is Persian Calender date into the java object, using JDBC.
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
Have a look at these

java.util.Calender
java.util.Locale
java.util.TimeZone

cheers.
David Harkness
Ranch Hand

Joined: Aug 07, 2003
Posts: 1646
If you're accessing the column using getDate/Timestamp (in other words, not as a formatted strnig), I'd bet that Oracle's calendar doesn't come into play. It should only be used when Oracle formats dates for display.

You could either select it as a string -- to_char(column) -- or set up a DateFormat in Java to format the retrieved Date for display. Also note that the Calendar class is only used to operate on Dates, not format them.

A java.util.Date is merely a long integer representing a number of milliseconds relative to the epoch: January 1, 1970, IIRC (see Date class). Calendars are used to modify Dates and DateFormats to parse and format them. Any particular date on the Gregorian calendar and its equivalent date on the Persian calendar should have the same long value in the Date object.

I hope I haven't made it more confusing.
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
Yes David I agree.

Naveen,
However, for formatting the date we can use java.text.SimpleDateFormat, as well. But now its not about formating at all. It would come to that when you try David's suggestion.
[ January 14, 2005: Message edited by: Adeel Ansari ]
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
Ok, try this,

rs.getString(n) // n is +ve integer value

instead of,

rs.getDate(n) // n is +ve integer value

not sure though.
cheers.
[ January 14, 2005: Message edited by: Adeel Ansari ]
Naveen Kumar
Ranch Hand

Joined: May 29, 2002
Posts: 30
Hi Guys,

Thanks a lot for your help.
Well my requirement is not to display the DATE in some specfig format.
The main concern over here is the Calendar.
Persian Calendar is not supported in by Sun JDK, we need to develop this calendar functionality.

My application requires the following. The users are allowed to access the application based on a locale. Here the DB is by default configured for Persian Calender. So the persian users should be able to see the date in Persian calendar and English using Gregorian.

By default Oracle stores the date in a standard format. Where the data retrieved depends on the Calender format choosen by the client. So by default the JDBC client is retrieving the date in Gregorian Calender.

What I need is to customize this based on the locale. Might be the connection to be initialized with some parameters etc.

Please do let me know what i can do to override Gergorian and get the data in specific calender based.

Thanks & Regards

Naveen
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
Have you tried out ours suggestions?
David Harkness
Ranch Hand

Joined: Aug 07, 2003
Posts: 1646
Originally posted by Naveen Kumar:
Well my requirement is not to display the DATE in some specfig format.
The main concern over here is the Calendar.
...
So the persian users should be able to see the date in Persian calendar and English using Gregorian.
Please (re)read my description of the differences between Date and Calendar, for these statements don't make sense to me. Dates are Calendar-agnostic. A Date is the number of milliseconds that has elapsed since a specific fixed point in time (the epoch).

If you retrieve a Data from Oracle as Adeel recommended, it should be the same value whether the user is Persian or English. It's just a number. The Calendar comes in to play when you need to modify a Date and possibly when formatting it (I haven't checked to see if SimpleDateFormat uses a Calendar under the hood, though I figure it must).

Also, you say you don't need to display the Date and then follow it up by saying that Persian users should see Dates formatted using the Persian Calendar -- that sounds like display to me!

As far as I know, Oracle works in a similar fashion. The date column value is a calendar-agnostic number. When you set the calendar in Oracle, you're telling it how to display dates (format them as strings). This should have no bearing on your Java program as you should be accessing the raw DATE -- not a formatted string.
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1125

Naveen,
In case you are unaware of it, this may be of help:

http://oss.software.ibm.com/icu/userguide/dateCalendar.html

Good Luck,
Avi.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Reading date from Oracle based on calendar