OraFAQ Forum: SQL & PL/SQL » One year minus on Date

Home » SQL & PL/SQL » SQL & PL/SQL » One year minus on Date (Form 6i, Oracle 9i)

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

One year minus on Date [message #397510]

Sat, 11 April 2009 12:54

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

Report message to a moderator

Re: One year minus on Date [message #397511 is a reply to message #397510]

Sat, 11 April 2009 13:00

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: One year minus on Date [message #397513 is a reply to message #397510]

Sat, 11 April 2009 13:12

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I bet Forms6 does not know numtoyminterval function.
Use add_months one.

Regards
Michel

Report message to a moderator

Re: One year minus on Date [message #397514 is a reply to message #397510]

Sat, 11 April 2009 13:25

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

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: One year minus on Date [message #397517 is a reply to message #397516]

Sat, 11 April 2009 13:49

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

Report message to a moderator

Previous Topic:	invalid data inserted
Next Topic:	ORA-24338: statement handle not executed

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 23 13:09:58 CDT 2025