Home » Developer & Programmer » Reports & Discoverer » Date parameter diff return wrong calculation (report 6i, windows)
Date parameter diff return wrong calculation [message #686692] Wed, 23 November 2022 09:53 Go to next message
hissam78
Messages: 193
Registered: August 2011
Location: PAKISTAN
Senior Member
Dear experts,
We are using the following date Parameters in report :d1 and :d2 and using the following formula to return no. of months

Entering 01-jul-2022 in :d1 and 30-dec-2022 in :d2

RETURN (trunc(MONTHS_BETWEEN(:D2,:D1),12));

it return 6 months that's ok,
but when we multiply 6 with some value e.g 28000, it return wrong calculation,

some body can help us why it is like that,


regards,
icon1.gif  Re: Date parameter diff return wrong calculation [message #686694 is a reply to message #686692] Fri, 25 November 2022 01:31 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Perhaps you are hitting the limits of the date data type? 28000 times 6 months would take you back to about 12000 BC, and you can't go that far:
 orclz>
orclz> select sysdate, sysdate - 6*31 from dual;

SYSDATE             SYSDATE-6*31
------------------- -------------------
2022-11-25:07:27:41 2022-05-23:07:27:41

orclz> select sysdate, sysdate - 6*31*28000 from dual;
select sysdate, sysdate - 6*31*28000 from dual
                        *
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0


orclz>
Re: Date parameter diff return wrong calculation [message #686696 is a reply to message #686694] Fri, 25 November 2022 10:02 Go to previous message
hissam78
Messages: 193
Registered: August 2011
Location: PAKISTAN
Senior Member
Thanks a lot John, Understood
Previous Topic: report
Next Topic: 12c report Error while calling report with PARAMFORM=YES
Goto Forum:
  


Current Time: Thu Mar 28 10:34:13 CDT 2024