RE: ora-01841

Grace,

I assume you are doing the same test case in both environments. In addition to the other suggestions, take a look at your code and see where date1 and date2 get assigned within your program unit or trigger. I am guessing that a character value is being assigned to date1/date2 either explicitly or via a character column in a table, the form, a literal, whatever. And, when the assignment is done, no mask is specified and an implicit conversion is occurring. And, the NLS_DATE_FORMAT in effect in your c/s environment is different from the one in effect in your web deployed environment. This is all just conjecture; but, I think it's worth pursuing.

Here is the background of why I think it is worth pursuing:

1. I am assuming that date1 and date2 are local *date* variables in your trigger or program unit. I say this because they do not have the :block.item notation that would be used if referencing a block's item. Additionally, I'm thinking they are date variables because if they char/varchar2 variables, you would get the "too many declaration of 'TO_CHAR' match this call" when trying to compile the trigger.
2. Because of a difference in the NLS_DATE_FORMAT settings in effect, things work fine c/s but not on the web deployed forms. Here is a simple example showing how differences in the NLS_DATE_FORMAT can produce the error message you are seeing when no mask is specified during the assignment:

SQL> alter session set nls_date_format = 'DD-MON-YY';

Session altered.

SQL> declare
  2 date1 date := '01-jan-00';
  3 begin
  4 null;
  5 end;
  6 /

PL/SQL procedure successfully completed.

SQL> save c:\foo
Created file c:\foo
SQL> alter session set nls_date_format = 'DD-MON-YYYY';

Session altered.

SQL> @c:\foo
declare
*
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0 ORA-06512: at line 2

So, this is just a guess; but, I am thinking that maybe your c/s setting is DD-MON-YY (or not set at all thus defaulting to it), and, in the web environment, it might be something like DD-MON-YYYY. Or variations of such where one specifies two digits for the year where the other specifies 4 digits. I would expect that your code is blowing up where either date1 or date2 gets *assigned* a value, not where the comparison is done. So, track down where that happens. Is a literal used and no mask specified? Is a character field or variable assigned to it, once again doing the conversion without specifying a mask? I tend to fully qualify the mask and include all four digits for the year when converting from character to date or vice versa.

Also note Diana's comment about comparing the items. If the variables truly are date variables, you do not need to do the TO_CHAR to compare the dates. And, as Diana also mentioned, if you are concerned about time components on them, simply using the TRUNC function as she described is a great way to go.

If the above is confusing, drop me a line and we can go into more detail. If you like, feel free to send directly to me the fmb and I will be happy to review it and help you out.

Regards,

Larry G. Elkins
elkinsl_at_flash.net

-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of grace lim Sent: Thursday, December 28, 2000 11:01 PM To: Multiple recipients of list ORACLE-L Subject: ora-01841

gurus,

i need ur help.... i have a forms that compare 2 dates . when i run it as client server, no error occur but when i transferred it to my nt web server application and run it from the web browser, an error occur

ora-01841:full year must be between -4713 and +9999 and not be 0.

here's a sample code
  if to_char(date1,'MMDDYYYY') = to_char(date2,'MMDDYYYY') then

     do some actions
  end if;

is there something wrong w/ the stmt? what do i have to do? Received on Fri Dec 29 2000 - 18:19:28 CST