After many tries I have to ask for your help:

select * from kalender where
StartTime - interval '3' DAY > CURRENT_DATE

works fine while this:

select * from kalender where
StartTime - interval DaysBefore DAY > CURRENT_DATE

does not work.

What am I doing wrong? (DaysBefore is an integer - StartTime a DateTime
field).

Thanks. Uli

Uli


Trying to test in EDBManager, in a query, I get the following error message

ElevateDB Error #700 An error was found in the statement at line 1 and column 34 (Expected Interval Day, Interval Day To Hour, Interval Day To Minute, Interval Day To Second, Interval Day To MSecond, Interval Hour, Interval Hour To Minute, Interval Hour To Second, Interval Hour To MSecond, Interval Minute, Interval Minute To Second, Interval Minute To MSecond, Interval Second, Interval Second To MSecond, or Interval MSecond expression but instead found INTERVAL '?' DAY)

So I suspect that parameters just aren't allowed.

I'm guessing that you're trying to use a parameterised query not a script.

Roy Lambert [Team Elevate]

Roy,

> I'm guessing that you're trying to use a parameterised query not a script.

I don't use any parameters. Just the simple query I posted. (DaysBefore
is an integer-field of the same table).

Uli

Uli


Sorry - I misunderstood. But I get exactly the same error message so it looks as though only constants are allowed presently. It does seem a strange limitation so either its a bug or its a strange limitation

Roy Lambert [Team Elevate]

Uli,

SELECT *
FROM Kalender
WHERE StartTime - CAST(DaysBefore AS INTERVAL DAY) > CURRENT_DATE


--
Fernando Dias
[Team Elevate]

Fernando,

> SELECT *
> FROM Kalender
> WHERE StartTime - CAST(DaysBefore AS INTERVAL DAY) > CURRENT_DATE

Thanks. That's it.
Though I am not sure why "DaysBefore" has to casted in this way. Is
there a rule or any hint in the manual?

Uli

I've suggested to Tim as part of an email conversation that INTERVALs would make a good subject for one of his technical articles.


I don't know about anyone else but they baffle the hell out of me.

Roy Lambert

Roy,

> I've suggested to Tim as part of an email conversation that INTERVALs would make a good subject for one of his technical articles.

Excellent idea!

> I don't know about anyone else but they baffle the hell out of me.

Now there are already two of us.

Uli

Uli,

EDB manuals don't elaborate much around this, maybe because it's standard SQL
2003 (and SQL:1999) syntax, however Interval types are extensively referenced:

http://www.elevatesoft.com/manual?action=mantopic&id=edb2sql&category=2&topic=35

I don't knows if it deserves an article, as Roy suggested, but I agree that at
least some examples in the manual could be helpful.

--
Fernando Dias
[Team Elevate]

Uli,

> Though I am not sure why "DaysBefore" has to casted in this way.

Exactly for the same reason why you can't subtract Integers and CHARs, i.e.
because it's an Integer column - if it was defined as an INTERVAL DAY column it
wouldn't be necessary to do a type conversion.

--
Fernando Dias
[Team Elevate]