-
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
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
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
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');
Sonali
-
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]
Sonali
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|