• 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

Reading date from Oracle based on calendar

 
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 2874
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Have a look at these

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

cheers.
 
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 2874
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 2874
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 30
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 2874
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Have you tried out ours suggestions?
 
David Harkness
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
 
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
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.
 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
@Naveen: I am facing the same problem. Could you please help me and provide the solution.
 
Marshal
Posts: 79153
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Welcome to the Ranch

This is an old thread and the original poster (=OP) will probably not reply. Please explain more about your query.
 
Tejeshvi Roy
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
In my application(in struts), we need to support Persian Calendar.

When we submit a form, date is coming as String in Action class. We need to save this date in Persian format in DB. We have configured DB for Persian Calendar. And while retrieving data from DB user should be able to see the date in Persian format only.

Also, the user can switch in 2 languages(English, Persian). So, application should support both type of calendars(Gregorian and Persian). If user logged-in in English, Gregorian calendar should be visible. If user logged-in in Persian language, then Persian Calendar should be visible.

For date conversion from Gregorian to Persian I am using below: http://www.dailyfreecode.com/forum/converter-gregorian-date-jalali-date-20288.aspx

In above requirement, I am facing 2 issues:

While submitting a form, how can we save date(which is in String format in Action class) in Persian format in DB?
While retrieving data from DB, it should come in Persian format. As of now, the JDBC client is retrieving the date in Gregorian Calender.
How can we overcome the above issues? Thanks in advance.
 
Tejeshvi Roy
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Regarding 2nd issue,

I am passing java.sql.Date(today's date) which is getting saved in persian format in DB. Using below code.

java.sql.Date sqlDate = null;
java.util.Date utilDate = new Date();
sqlDate = new java.sql.Date(utilDate.getTime());

PreparedStatement psmtInsert = conn.prepareStatement(insertQuery);
psmtInsert.setDate(1, sqlDate));
psmtInsert.executeUpdate();


But while retrieving data from DB, it should come in Persian format. As of now, the JDBC client is retrieving the date in Gregorian Calender.
 
reply
    Bookmark Topic Watch Topic
  • New Topic