aspose file tools*
The moose likes JDBC and the fly likes Strange behaviour when using Oracle TO_CHAR to get day of week Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Strange behaviour when using Oracle TO_CHAR to get day of week" Watch "Strange behaviour when using Oracle TO_CHAR to get day of week" New topic
Author

Strange behaviour when using Oracle TO_CHAR to get day of week

Sonny Gill
Ranch Hand

Joined: Feb 02, 2002
Posts: 1211

G'day!

I am getting two different results when executing a statement using JDBC, and when running the same query using an Oracle client(I have tried SQL Worksheet and Tora).

When I run the following query-


Using JDBC, I get
5, Friday
Indicating that it is taking Monday as the 1st day of the week.

When running directly from an Oracle client, I get
6, Friday
i.e. Sunday is being considered the first day of the week.

I am using everything with default configuration. Any ideas?

Thanks
Sonny
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
wiered!

i applied the same query. but i am getting the same results from both JDBC and ORACLE Client.

ORACLE CLIENT:
- SQL Plus.
- ORCACLE 10g
- Windows

JDBC:
- classes12.zip
- oracle.jdbc.driver.OracleDriver
- jdbc racle:thin:@

Both results in:
6, Friday
[ September 10, 2004: Message edited by: adeel ansari ]
Jyothi Lature
Ranch Hand

Joined: Oct 31, 2001
Posts: 60
Hi,

I guess the problem is, the DATE set on database server and application server machines are different (am assuming both are installed on different machines)...!!


Jyothi<br />Sun Certified Java Programmer<br />Brainbench Certified for Javascript
Sonny Gill
Ranch Hand

Joined: Feb 02, 2002
Posts: 1211

My guess is that it has something to do with the Locale settings. The locale settings used by JDBC treat Monday as the first day, whereas native Oracle settings treat Sunday as the first day.

Could someone please help point me in the right direction about how to handle locale settings when using Oracle through JDBC.

Feverishly googling meanwhile
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
wht are you using a prepared statement or a statement?? i used Statement.
and i dont think it would be a locale problem.

are you damn sure that oracle treats sunday as a first day?
[ September 10, 2004: Message edited by: adeel ansari ]
Sonny Gill
Ranch Hand

Joined: Feb 02, 2002
Posts: 1211

I am using a prepared statement, although that shouldnt make a difference IMO.

I dont know what the default for Oracle is, but you can set Locale settings for Oracle.

For example
export NLS_LANG=AMERICAN
sets the default languages to be used.

I think that the locale settings used by my Oracle client are different from those used by my JDBC setup, although I dont know how to check it for sure at this stage.
Sonny Gill
Ranch Hand

Joined: Feb 02, 2002
Posts: 1211

Oh well, for now I am just running a query to see what day the database is using as the 'first day of week', and code my logic accordingly. But I hope, someone can point out the 'best practice' for something like this.

Thanks for the input, guys
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
we both are gettin "6, Friday" with Oracle client. so, the problem is why you are gettin 5 when using jdbc.

yeah we can set the value for NLS_LANG parameter for oracle. probably you can find that parameter in your database parameter file. under /ORAHOME/..

wish you luck
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Strange behaviour when using Oracle TO_CHAR to get day of week