OraFAQ Forum: SQL & PL/SQL » Date function problem(Interval adding)

Home » SQL & PL/SQL » SQL & PL/SQL » Date function problem(Interval adding)

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

Date function problem(Interval adding) [message #133054]

Wed, 17 August 2005 08:51

Achchan
Messages: 86
Registered: June 2005

Member

Hi all,
I have a table with a column named interval(Unfortunately! )and another column named start_time.
I want to submit a select query against this table like this one:
SELECT * FROM TABLE1
WHERE (start_time + INTERVAL 'interval' minute) < systimestamp;

As you see the lowercase interval is the name of that column but It gots error:
PL/SQL: ORA-01867: the interval is invalid

What should I do?

Report message to a moderator

Re: Date function problem(Interval adding) [message #133140 is a reply to message #133054]

Wed, 17 August 2005 16:35

mchadder
Messages: 224
Registered: May 2005
Location: UK

Senior Member

Hello.

Well, for a start, you're specify 'interval' as the interval! The string component of the INTERVAL has to be a valid number. Given that, however, you can't reference a column in the INTERVAL syntax anyway, i.e.

SQL> CREATE TABLE x ( interval  VARCHAR2(10) );

Table created.

SQL> SELECT SYSDATE + INTERVAL x.interval MINUTE
  2    FROM x;
SELECT SYSDATE + INTERVAL x.interval MINUTE
                           *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

The INTERVAL parameter has to be "hardcoded", i.e.

SQL> SELECT SYSDATE + INTERVAL '10' MINUTE
  2    FROM dual;

SYSDATE+INTERVAL'10'
--------------------
17-AUG-2005 22:44:00

I think you're going to have to carry on with the "old" mechanism of :

SQL> INSERT INTO x VALUES ( '10' );

1 row created.

SQL> SELECT SYSDATE + ( x.interval / 60 / 24 ) 
  2    FROM x;

SYSDATE+(X.INTERVAL/
--------------------
17-AUG-2005 22:44:52

Rgds

Report message to a moderator

Re: Date function problem(Interval adding) [message #133142 is a reply to message #133054]

Wed, 17 August 2005 16:41

mchadder
Messages: 224
Registered: May 2005
Location: UK

Senior Member

Hello.

Actually, just remembered a way you CAN do it, using the
NUMTODSINTERVAL function, i.e.

SQL> SELECT SYSDATE + NUMTODSINTERVAL(x.interval, 'MINUTE')
  2    FROM x;

SYSDATE+NUMTODSINTER
--------------------
17-AUG-2005 22:49:57

Rgds

Report message to a moderator

Re: Date function problem(Interval adding) [message #133616 is a reply to message #133142]

Sat, 20 August 2005 02:47

Achchan
Messages: 86
Registered: June 2005

Member

Thanks alot,That was so useful.

Report message to a moderator

Previous Topic:	compare two procedures
Next Topic:	Group By Problem (merged 3 threads)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 12 04:28:02 CDT 2026