date format

I have a German Customer who is having trouble with date formats, it keeps giving invalid day of the week or invalid month error message.
I have tried following date formats for him:

dd- mon - yyyy
dd-mm-yyyy
dd-mm-yyyy hh24:mi:ss
YYYY-MM-DD HH:MI:SS AM
etc
none of them work through stored procedure, gives error but when these statements are run thro' application directly the date coversion works.

CREATE OR REPLACE PROCEDURE pr_authpto
(inResID Int,inWorkID Int, strStartDate varchar2, strEndDate varchar2)
IS
/* Declare Local Variable */
dtDate date;
dtStartDate date;
dtEndDate date;

Begin
dtStartDate := to_date(strStartDate,'YYYY-MM-DD "00:00:00.000"');
dtEndDate := to_date(strEndDate, 'YYYY-MM-DD "00:00:00.000"');

.... rest of the procedure...
We pass this date as string and then inside the procedure we convert it to date, this fails.

Execute pr_authpto (552, 570, '2001-07-22 00:00:00.000', '2001-07-28 00:00:00.000');

But statements thro' the application which is on web but uses
odbc works !
eg:
SELECT Res_Date_Ext6 FROM mwebRes WHERE Res_ID = 552 and (to_date('2001-07-23 12:00:00 AM','YYYY-MM-DD HH:MI:SS AM') > Res_Date_Ext6 or to_date('2001-07-23 12:00:00 AM','YYYY-MM-DD HH:MI:SS AM') = Res_Date_Ext6) and Res_Date_Ext6 is not null.


They have oracle server on german windows 2000 machine.
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

Please any help
Thanks
Sonali

The format, "00:00:00.000", is wrong.
Should be: "HH:MI:SS"

Yes, I have even tried that as I have said, all date format fails thro' procedure.

thanks
Sonali

what eroror message do you get ???
AFAIK, you cannot specify anything smaller than a second ...
try :
dtStartDate := to_date(strStartDate,'YYYY-MM-DD HH24:MI:SS')
and send '2001-08-01 10:40:00'

He gets invalid day of the week or invalid month error message.
yes I have tried all those formats, none of them work..
dd- mon - yyyy
dd-mm-yyyy
dd-mm-yyyy hh24:mi:ss
YYYY-MM-DD HH:MI:SS AM


Sonali

what EXACTLY is the strStartDate you send in ???

strStartdate is varchar2 variable so we send that in as a string.

We convert that to date once it gets inside the procedure, this was designed only to avoid these errors may be due to oracle nls settings.

Strstartdate format for string matches exactly with to_date conversion inside the procedure. this is where it is failing with error like invalid day of week and invalid month errors.

This is how this string is passed in
if you look at my 1st post I had posted both part of stored procedure and a execute statement.
Another eg, that I have tried

CREATE OR REPLACE PROCEDURE pr_authpto
(inResID Int,inWorkID Int, strStartDate varchar2, strEndDate varchar2)
IS
/* Declare Local Variable */
dtDate date;
dtStartDate date;
dtEndDate date;

Begin
dtStartDate := to_date(strStartDate,'YYYY-mm-dd hh24:mi:ss'
);
dtEndDate := to_date(strEndDate, 'YYYY-mm-dd hh24:mi:ss' );

.... rest of the procedure...
We pass this date as string and then inside the procedure we convert it to date, this fails.

Execute pr_authpto (552, 570, '2001-07-22 00:00:00', '2001-07-28 00:00:00');

I have additional information today on this from that German customer.

Note again that he has Oracle 8.1.6.0.0 on german windows 2000 machine.

Here are few of them that we have tried:

'YYYY-MM-DD "00:00:00.000"'

DD-MON-YYYY

'YYYY-MM-DD HH24:MI:SS'

'YYYY-MM-DD HH:MI:SS AM'

'YYYY-MM-DD'

Using Oracle worksheet these stored procedure works, but none thro' Oracle ODBC test32.
So may be ODBC driver is a culprit or may be a German server.
They have Oracle's ODBC driver version 8.01.06.00.

They didn't change date format for their database, but when making
SELECT-queries it appears as dd.mm.yy.

We use similar date formats in sql statements too in our web application.

One thing which puzzled me is that,
All of the stored procedures where the date is passed in as a varaiable fail thro' odbc test, our application. But they work if execute is done thro' oracle worksheet.

Also, the sql statements other than stored procedures which uses similar date formats in our application work thro' application.
Then why would these statements work and not stored procedure !

I had also pasted part of their v$parameter file in the 1st post, if that would help.
Thanks a lot for help
Sonali







[Edited by sonaliak on 08-01-2001 at 09:26 AM]

if you get invalid day of week, it means that the error does not come from your conversion to date, since you don't use day of week format (d)
furthermore, there is no reason for your procedure not to work, I guess there is another date manipulation further which produces the error ...

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

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 ?

Quote:

---

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 ?

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

How do I do that ?

How do I find what the default nls_date_language is ?

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

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.