This week's giveaway is in the Spring forum.
We're giving away four copies of Microservices Testing (Live Project) and have Chris Love & Andres Sacco on-line!
See this thread for details.
Win a copy of Microservices Testing (Live Project) this week in the Spring forum!
  • 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
  • Tim Cooke
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Liutauras Vilda
  • Henry Wong
  • Devaka Cooray
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Al Hobbs
  • Carey Brown
Bartenders:
  • Piet Souris
  • Mikalai Zaikin
  • Himai Minh

Strange behaviour when using Oracle TO_CHAR to get day of week

 
Ranch Hand
Posts: 1211
Mac IntelliJ IDE
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
Ranch Hand
Posts: 60
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Mac IntelliJ IDE
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Mac IntelliJ IDE
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Mac IntelliJ IDE
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Alas, poor Yorick, he knew this tiny ad:
Free, earth friendly heat - from the CodeRanch trailboss
https://www.kickstarter.com/projects/paulwheaton/free-heat
reply
    Bookmark Topic Watch Topic
  • New Topic