Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01841: (full) year must be between -4713 and +9999, and not be 0
ORA-01841: (full) year must be between -4713 and +9999, and not be 0 [message #250437] Tue, 10 July 2007 01:08 Go to next message
mandesh
Messages: 2
Registered: July 2007
Junior Member
Hi,

i am back again .. this time a new error message --

ORA-01841: (full) year must be between -4713 and +9999, and not be 0 when i am trying to create the following function -



CREATE OR REPLACE FUNCTION CREATE_VIEW_FT_IB_DETAILS (REPORTDATE IN DATE ) RETURN NUMBER I
SQL_STMT VARCHAR2(1000);
REPORTDT VARCHAR2(50);
FROMDT DATE;
TODT DATE;
BEGIN

BEGIN
FROMDT := TRUNC(TO_DATE(REPORTDATE,'MM/DD/YYYY'),'MONTH');
TODT := LAST_DAY(TO_DATE(REPORTDATE,'MM/DD/YYYY'));
REPORTDT := TO_CHAR(TO_DATE(REPORTDATE,'MM/DD/YYYY'), 'YYYYMM');
SQL_STMT:= 'CREATE OR REPLACE VIEW VW_FT_INBOUND_DETAILS_'
||' AS SELECT PID,'
||'TRANSACTION_AMOUNT, '
||'TO_CHAR (TRANSACTION_DATE,''YYYY/MM/DD'') TRANSACTION_DT,'
||'FROM TEMP_TABLE '
||'WHERE '
||'TXN_DATE BETWEEN TO_DATE('
|| ''''
|| FROMDT
|| ''''
|| ',''MM/DD/YYYY''),''MONTH'') AND TO_DATE('
|| ''''
|| TODT
|| ''''
|| ',''MM/DD/YYYY'')';

EXCEPTION
WHEN OTHERS THEN
ORA_RAISERROR (SQLCODE,'ERROR WHILE ASSIGNING VALUE', 40);
END;

BEGIN
EXECUTE IMMEDIATE SQL_STMT;
RETURN 0;
EXCEPTION
WHEN OTHERS THEN
ORA_RAISERROR (SQLCODE,'ERROR WHILE EXECUTING STATEMENT',50);
END;
EXCEPTION
WHEN OTHERS THEN
ORA_RAISERROR (SQLCODE,'ERROR WHILE CREATING VIEW',50);

END;



I am entering the date as to_date('07/01/2007','mm/dd/yyy'). can you pls help?

Re: ORA-01841: (full) year must be between -4713 and +9999, and not be 0 [message #250441 is a reply to message #250437] Tue, 10 July 2007 01:20 Go to previous messageGo to next message
boyet-x
Messages: 23
Registered: August 2006
Junior Member
Your year format is incorrect --> to_date('07/01/2007','mm/dd/yyy')
Re: ORA-01841: (full) year must be between -4713 and +9999, and not be 0 [message #250445 is a reply to message #250437] Tue, 10 July 2007 01:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: ORA-01841: (full) year must be between -4713 and +9999, and not be 0 [message #250459 is a reply to message #250437] Tue, 10 July 2007 02:09 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Hi,

FROMDT is a DATE
'some string '||FROMDT||' another string' is a VARCHAR2; FROMDT is implicitely converted by your actual NLS_DATE_FORMAT setting

In my opinion you should avoid this construction and use binding instead.
Re: ORA-01841: (full) year must be between -4713 and +9999, and not be 0 [message #250846 is a reply to message #250437] Wed, 11 July 2007 10:00 Go to previous message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
If I'm reading this right, your portion of the statement...

||'WHERE '
||'TXN_DATE BETWEEN TO_DATE('
|| ''''
|| FROMDT
|| ''''
|| ',''MM/DD/YYYY''),''MONTH'') AND TO_DATE('
|| ''''
|| TODT
|| ''''
|| ',''MM/DD/YYYY'')';


will add this to the statement you're building...

 TXN_DATE BETWEEN TO_DATE('FROMDT','MM/DD/YYYY'),'MONTH') AND TO_DATE('TODT','MM/DD/YYYY');


which doesn't look like a valid "where" clause to me. My recommendation for you is to print out the SQL_STMT variable before you perform the "execute immediate" to make sure that you have a valid SQL statement to submit. I think when you do that, you'll see your errors.

What I see here is that you have 'FROMDT' in your to_date statement and you don't need the quotes since FROMDT is already of type DATE. So, your statement "TO_DATE('FROMDT','MM/DD/YYYY')" says "Take the letters FROMDT, and format them in MM/DD/YYYY format", which, of course, is impossible. The statement "TO_DATE(FROMDT,'MM/DD/YYYY')" says, take the contents of the variable FROMDT, which is of type DATE, and format it in MM/DD/YYYY format".

In fact, since you already define FROMDT and TODT with the truncate command, you should probably be able to say

||'WHERE '
||'TXN_DATE BETWEEN '
||'FROMDT '
||'AND '
||'TODT'
||';'



In any event, good luck with this query.

Ron
Previous Topic: using chr(10) properly
Next Topic: Trying to get info from Date field
Goto Forum:
  


Current Time: Fri Dec 09 21:28:00 CST 2016

Total time taken to generate the page: 0.06006 seconds