• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Strange behaviour when using Oracle TO_CHAR to get day of week

 
Sonny Gill
Ranch Hand
Posts: 1211
IntelliJ IDE Mac
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 60
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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)...!!
 
Sonny Gill
Ranch Hand
Posts: 1211
IntelliJ IDE Mac
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1211
IntelliJ IDE Mac
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1211
IntelliJ IDE Mac
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic