date format

[sonaliak]

Hi Pipo,
Thanks for all your help till now, no we do not do any other date coversion inside that procedure and most of them.
The error says that it failed on the 1st line which is that.
Furthermore, I did wanted to confuse everyone, but now that you have raised this, there is a metalink article which says that, German oracle dates can cause this type of error message:
Bug number: 727070, updated on 29-nov-2000
If you run any of these on Mon, Fri, Sat you get Invalid Month error.
On Tue, Wed, Thu, Sun you get invalid day of the week error.

This is exactly what I am observing, different errors on perticular days !!!!!

But looks like everything else in this article does not exactly match with what we have.

Here is the output from their v$parameter file:
NUM NAME TYPE ISDEFAULT ISMODIFIED ISADJ VALUE
60 nls_language 2 TRUE FALSE FALSE AMERICAN
61 nls_territory 2 TRUE FALSE FALSE AMERICA
62 nls_sort 2 TRUE FALSE FALSE
63 nls_date_language 2 TRUE FALSE FALSE
64 nls_date_format 2 TRUE FALSE FALSE
65 nls_currency 2 TRUE FALSE FALSE
66 nls_numeric_characters 2 TRUE FALSE FALSE
67 nls_iso_currency 2 TRUE FALSE FALSE
68 nls_calendar 2 TRUE FALSE FALSE
69 nls_time_format 2 TRUE FALSE FALSE

Here they have nls_language set to American
nls_territory to america
but nls_date_language is set to default, I do not know as his machine is German would this default be German ? As init.ora setting for this has not been done, it shows defult there, can this be the problem ?

[sonaliak]

Originally posted by truma1
Have you checked to see if your session's NLS_DATE format is the same as the instance's NLS_DATE format

No, not as yet, how do I do that ?

[isaady]

Also check for the uniqueness in the NLS_LANG character set both in the server as well as the instances....

[sonaliak]

How do I do that ?

How do I find what the default nls_date_language is ?

[isaady]

I am sorry for this delayed response...

The NLS_LANG can be checked from the registry under the Oracle key. ALso the env. parameter defined in the profile of ur server too specifies the NLS_LANG value.

Also, u can check it out from ur DB by issuing the

SQL>SHOW PARAMETERS

this would list u the parameters that has been set on installation of ur s/w

[truma1]

If you are using SQL Plus
try "select sysdate from dual;" this will give you the format of you session NLS_DATE. If this differs from your instance NLS_DATE format i.e. hsow parameter NLS_DATE then type
"alter session set NLS_DATE ...." to change your date format.