OraFAQ Forum: Reports & Discoverer » Date parameter diff return wrong calculation

Home » Developer & Programmer » Reports & Discoverer » Date parameter diff return wrong calculation (report 6i, windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Date parameter diff return wrong calculation [message #686692]

Wed, 23 November 2022 09:53

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,

Report message to a moderator

Re: Date parameter diff return wrong calculation [message #686694 is a reply to message #686692]

Fri, 25 November 2022 01:31

John Watson
Messages: 8964
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>

Report message to a moderator

Re: Date parameter diff return wrong calculation [message #686696 is a reply to message #686694]

Fri, 25 November 2022 10:02

hissam78
Messages: 193
Registered: August 2011
Location: PAKISTAN

Senior Member

Thanks a lot John, Understood

Report message to a moderator

Previous Topic:	report
Next Topic:	12c report Error while calling report with PARAMFORM=YES

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 06 22:35:08 CST 2025