Home » Developer & Programmer » Forms » Date Function Ora-01841(full) year must be between (OEL 5.5 , Oracle 11.2.0.2.0)
Date Function Ora-01841(full) year must be between [message #510177] Thu, 02 June 2011 14:09 Go to next message
s197oo302
Messages: 50
Registered: January 2011
Location: seoul
Member
My Test server this procedure working fine.
But the real server it shows error Ora-01841 (full) year must be between -4713 and +9999, and not be 0.
I assumed some problem on To_Char conversion and year resulted as '0000'. I couldn't know exactly.
Other concern is why one server(test server) working fine and the real server had error.
Here's my procedure.
CREATE OR REPLACE PROCEDURE ORAASFS.P_TM_SFS_SMS_RMV9
  IS
 current_dt VARCHAR2(14);

        CURSOR get_day IS
        SELECT VAL FROM TC_S6T_DEL_CFG WHERE PARM = 'SFSSMSSTOREDAYS';
   
      v_get_day TC_S6T_DEL_CFG.VAL%TYPE;
      v_day     NUMBER;
BEGIN
    OPEN get_day;
    FETCH get_day INTO v_get_day;
    v_day := TO_NUMBER(v_get_day);
    current_dt := TO_CHAR((CURRENT_DATE - v_day), 'YYYYMMDDHHMISS');
  IF v_day <= 3 THEN                                                                                                
delete from  TM_SFS_SMS_001 where SMS_CLC < TO_CHAR(CURRENT_DATE -3,'YYYYMMDDHHMISS');           
 ELSIF 3 < v_day and v_day < 100 THEN
delete from  TM_SFS_SMS_001 where SMS_CLC < TO_CHAR(CURRENT_DATE - current_dt,'YYYYMMDDHHMISS'); 
 ELSE 
  delete from  TM_SFS_SMS_001 where SMS_CLC < TO_CHAR(CURRENT_DATE - 100,'YYYYMMDDHHMISS');            
   END IF; 

    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            errpkg.report_and_go(SQLCODE, 'P_SFS_SMS_RMV No data times :: '||SYSDATE);
        WHEN OTHERS THEN
            errpkg.report_and_stop(SQLCODE, 'P_SFS_SMS_RMV Error times ::'||SYSDATE);

END P_TM_SFS_SMS_RMV9;
/

Report message to a moderator

Re: Date Function Ora-01841(full) year must be between [message #510178 is a reply to message #510177] Thu, 02 June 2011 14:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>But the real server it shows error Ora-01841 (full) year must be between -4713 and +9999, and not be 0.
which line is throwing this error

>Other concern is why one server(test server) working fine and the real server had error.
Something is different.

Report message to a moderator

Re: Date Function Ora-01841(full) year must be between [message #510187 is a reply to message #510177] Thu, 02 June 2011 15:27 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
s197oo302 wrote on Thu, 02 June 2011 15:09

 current_dt VARCHAR2(14);
      v_day     NUMBER;
    current_dt := TO_CHAR((CURRENT_DATE - v_day), 'YYYYMMDDHHMISS');


There is a conflict with that code.

Report message to a moderator

Re: Date Function Ora-01841(full) year must be between [message #510190 is a reply to message #510187] Thu, 02 June 2011 16:09 Go to previous messageGo to next message
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
Senior Member
 
TO_CHAR(CURRENT_DATE - current_dt,'YYYYMMDDHHMISS')

You're subtracting a varchar from a date.

You might also want to fix your data model. SMS_CLC needs to be a date.

Report message to a moderator

Re: Date Function Ora-01841(full) year must be between [message #510849 is a reply to message #510190] Wed, 08 June 2011 01:57 Go to previous messageGo to next message
s197oo302
Messages: 50
Registered: January 2011
Location: seoul
Member
You are 100% right.
SMS_CLC is VARCHAR2 and my senior force me to use VARCHAR2 instead of DATE because he think it is faster than use DATE to column format.
Test server was no problem because it didn't executed on problem sentence.
I change to current_dt from VARCHAR2 to DATE and i like to test again with your help.

The modified procedure working ok, I will test scheduler and post the result.
Thank you.

Edited by: user9359279 on Jun 8, 2011 3:55 PM

Report message to a moderator

Re: Date Function Ora-01841(full) year must be between [message #510857 is a reply to message #510849] Wed, 08 June 2011 02:17 Go to previous messageGo to next message
Littlefoot
Messages: 21813
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
my senior force me to use VARCHAR2 instead of DATE because he think it is faster than use DATE to column format.

Anti-example of a senior.

Report message to a moderator

Re: Date Function Ora-01841(full) year must be between [message #510870 is a reply to message #510849] Wed, 08 June 2011 02:58 Go to previous messageGo to next message
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
Senior Member
s197oo302 wrote on Wed, 08 June 2011 07:57

SMS_CLC is VARCHAR2 and my senior force me to use VARCHAR2 instead of DATE because he think it is faster than use DATE to column format.


Your senior doesn't have a clue what he's talking about.
If you only have one date format, then you can avoid format conversions sure, but they're lightning fast anyway.
However you promptly stop oracle from doing all sorts of date optimisations when looking up data. Oracle knows how many hours are in a day, how many days in a month etc. It can use that information to help speed up date searchs. But only if it knows that the data it's looking at is a date.

And of course you lose the ability to do any and all date arithmatic - or you will be doing format conversions anyway.

Report message to a moderator

Re: Date Function Ora-01841(full) year must be between [message #563221 is a reply to message #510177] Fri, 10 August 2012 01:39 Go to previous messageGo to next message
rjobaan
Messages: 1
Registered: August 2012
Junior Member
I have also the same error message

When I run the following query:

select DISTINCT
A.WACHT,
to_date(to_char(A,'dd-mm-yyyy HH24:MI:SS'),'dd-mm-yyyy HH24:MI:SS'),
B.START,
B.END,
B.START-(10/(24*60)),
B.END-10/(24*60)
from DIM_TIME B
INNER JOIN RAP A
ON to_date(to_char(A.WACHT,'dd-mm-yyyy HH24:MI:SS'),'dd-mm-yyyy HH24:MI:SS')BETWEEN B.START-10/(24*60) AND B.END-10/(24*60)
where TRUNC(B.START)between '5-8-2012' and '8-8-2012'

No issues

but as soon as i remove the where clause its giving the ORA-1841 error
select DISTINCT
A.WACHT,
to_date(to_char(A,'dd-mm-yyyy HH24:MI:SS'),'dd-mm-yyyy HH24:MI:SS'),
B.START,
B.END,
B.START-(10/(24*60)),
B.END-10/(24*60)
from DIM_TIME B
INNER JOIN RAP A
ON to_date(to_char(A.WACHT,'dd-mm-yyyy HH24:MI:SS'),'dd-mm-yyyy HH24:MI:SS')BETWEEN B.START-10/(24*60) AND B.END-10/(24*60)


If i dive into the code i already get error when running this query
select DISTINCT
B.START,
B.START-(10/(24*60))
from DIM_TIME B


but when adding a where clause its working again??
select DISTINCT
B.START,
B.START-(10/(24*60))
from DIM_TIME B
where TRUNC(B.START)between '5-8-2012' and '8-8-2012'

what am i doing wrong?

Report message to a moderator

Re: Date Function Ora-01841(full) year must be between [message #563238 is a reply to message #563221] Fri, 10 August 2012 06:15 Go to previous messageGo to next message
Littlefoot
Messages: 21813
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What is DIM_TIME.START column's datatype?

Report message to a moderator

Re: Date Function Ora-01841(full) year must be between [message #563244 is a reply to message #563238] Fri, 10 August 2012 06:28 Go to previous message
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
Senior Member
What's the point of this:
to_date(to_char(A,'dd-mm-yyyy HH24:MI:SS'),'dd-mm-yyyy HH24:MI:SS')

If A is date then that's the same as:
A


What date is '5-8-2012'? It could be one of two dates. You should to_date that with the correct format mask.

Report message to a moderator

Previous Topic: Form Save related Query
Next Topic: which exception to caught
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Jul 04 15:58:42 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.