Home » RDBMS Server » Server Administration » Date bug in oracle?
Date bug in oracle? [message #370344] Tue, 31 August 1999 11:57 Go to next message
bosse
Messages: 2
Registered: August 1999
Junior Member
I'm using 8.05 and have a very irritating problem:

If doing a select TO_CHAR(to_DATE('20000201', 'YYYYMMDD'),'D') from dual; you will get the number 2 (indicating that 1 of feb year 2000 is a tuesday, which is correct.

If you are doing something like htp.p((TO_CHAR(to_DATE('20000201', 'YYYYMMDD'),'D')); in a piece of code you will get the number 3 indicating wednesday which is very wrong!! How come????

Why is there a difference in the two different statements? There should not be, or?

tia!
Re: Date bug in oracle? [message #370346 is a reply to message #370344] Tue, 31 August 1999 13:08 Go to previous messageGo to next message
hmg
Messages: 40
Registered: March 1999
Member
here's is a possible solution for your problem:

SQL> alter session set nls_territory = GERMANY

Session altered.

SQL> select TO_CHAR(to_DATE('20000201', 'YYYYMMDD'),'D') from dual;

T
-
2

SQL> alter session set nls_territory = AMERICA;

Session altered.

SQL> select TO_CHAR(to_DATE('20000201', 'YYYYMMDD'),'D') from dual;

T
-
3
Re: Date bug in oracle? [message #370347 is a reply to message #370344] Tue, 31 August 1999 22:02 Go to previous messageGo to next message
KenHP
Messages: 6
Registered: August 1999
Junior Member
NLS_TERRITORY sets the default for the week numbering.
You must be using different instances.
Re: Date bug in oracle? [message #370348 is a reply to message #370344] Wed, 01 September 1999 04:53 Go to previous message
bosse
Messages: 2
Registered: August 1999
Junior Member
Thanks to you all,
appearantly it was the nls_territory that was the spooky thing. Because when you run the command select ... from dual; it gets the nls_territory settings from your local computer and when doing the htp.p(to_char...); it chooses the nls_territory setting in the database. It wasn't the easiest thing to come to think of...
Once again, Thanks a lot to you all!

..Another day of learning new stuff!
Previous Topic: PL/SQL cross-tab challenge
Next Topic: Passing a PL/SQL nested table like parameter
Goto Forum:
  


Current Time: Thu Mar 28 03:48:36 CDT 2024