Strange behaviour when using Oracle TO_CHAR to get day of week

[Sonny Gill]

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-

 
select TO_NUMBER(TO_CHAR(sysdate, 'D')) AS day_of_week, TO_CHAR(sysdate, 'DAY') AS full_day from dual; 

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]

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]

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]

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]

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]

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]

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]

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