Home » SQL & PL/SQL » SQL & PL/SQL » One year minus on Date (Form 6i, Oracle 9i)
One year minus on Date [message #397510] Sat, 11 April 2009 12:54 Go to next message
cvs_1984
Messages: 136
Registered: August 2007
Location: Punjab, INDIA
Senior Member

Hi,

I required to Select the Sysdate minus year for the Report.

When I try to SQL using numtoyminterval the result is came for it.

When I try to use the Code in Report it display the Error of Bad Bind Variable numtoyminterval.


How to use the numtoyminterval.

Regards,

C V S
Re: One year minus on Date [message #397511 is a reply to message #397510] Sat, 11 April 2009 13:00 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/143508/136107/

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.
Re: One year minus on Date [message #397513 is a reply to message #397510] Sat, 11 April 2009 13:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I bet Forms6 does not know numtoyminterval function.
Use add_months one.

Regards
Michel
Re: One year minus on Date [message #397514 is a reply to message #397510] Sat, 11 April 2009 13:25 Go to previous messageGo to next message
user2004
Messages: 33
Registered: April 2009
Member

Michel Sir has already provided the answer.

Try

select add_months(sysdate,-12) from dual ; 


instead of
select sysdate - numtoyminterval(1, 'YEAR') from dual;



or this way,

SELECT SYSDATE - interval '1' YEAR FROM DUAL;



[Updated on: Sat, 11 April 2009 13:31]

Report message to a moderator

Re: One year minus on Date [message #397516 is a reply to message #397514] Sat, 11 April 2009 13:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
if numtoyminterval is not known, I don't think "interval '1' YEAR" is: they have been introduced at the same time.

Regards
Michel
Re: One year minus on Date [message #397517 is a reply to message #397516] Sat, 11 April 2009 13:49 Go to previous message
user2004
Messages: 33
Registered: April 2009
Member
May be....!!
add_months one should be preferred...


@cvs_1984, try other options also and see what happens Smile
Previous Topic: invalid data inserted
Next Topic: ORA-24338: statement handle not executed
Goto Forum:
  


Current Time: Sat Dec 10 03:04:11 CST 2016

Total time taken to generate the page: 0.06728 seconds