Fernando,

> 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.

Thanks a lot - that made things clearer.

Uli

Uli,

<< Though I am not sure why "DaysBefore" has to casted in this way. Is there
a rule or any hint in the manual? >>

Fernando is exactly right - the reason that the cast is necessary is because
an INTEGER <> DAY-TIME INTERVAL.  You have to tell EDB that the integer is
supposed to be an interval by casting it as such.

--
Tim Young
Elevate Software
www.elevatesoft.com

Uli,

BTW, if you're only going to use the DaysBefore value in date/time
calculations, then it may pay to just make its type be INTERVAL DAY instead.
2.03 has an updated EDB Manager that allows for interval types to be
displayed and assigned properly, so it makes it much easier to deal with the
interval types.  The reason that this wasn't present earlier is that it
required some work-arounds in the EDB Manager due to the fact that Delphi
does not have any TFieldType enumerations for interval types, so as far as
Delphi is concerned, a YEAR-MONTH interval is an INTEGER, and a DAY-TIME
interval is a BIGINT.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim .

> BTW, if you're only going to use the DaysBefore value in date/time
> calculations, then it may pay to just make its type be INTERVAL DAY instead.

Great. After Fernando's answer I played around with it and "discovered"
all these types.

Uli

Also, as Tim pointed out to me there are functions to help with using them in Delphi eg


http://www.elevatesoft.com/manual?action=mancompmethod&id=edb2&product=d&version=7&comp=TEDBEngine&method=YearMonthIntervalToSQLStr

Personally I just think the standards committee had to much whacky baccy


Roy Lambert

Roy,

<< Personally I just think the standards committee had to much whacky baccy
>>

Nah, if you consider that SQL is a fairly type-safe language, it makes sense
that intervals would be considered distinct from other types, just like
dates and times are considered distinct from other types.  There *is* a
method to the madness.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>There *is* a
>method to the madness.

Certainly if you happen to be a green skinned alien from Tau Ceti with three eyes and at least 12 arms!

Roy Lambert

Tim,

> Fernando is exactly right - the reason that the cast is necessary is because
> an INTEGER <> DAY-TIME INTERVAL.  You have to tell EDB that the integer is
> supposed to be an interval by casting it as such.

I understand, but how about:

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

'3' is a VarChar.

Uli

Uli,

<< I understand, but how about:

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

'3' is a VarChar. >>

Not with the INTERVAL keyword in front of it.

INTERVAL constants are declared like dates/times, in single quotes:

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

The reason for this is that they can contain formatting characters like ':'
or '.' and spaces.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>INTERVAL constants are declared like dates/times, in single quotes:

This proves not to be the case

select * from diary where
_day - interval 3 DAY > CURRENT_DATE

and

select * from diary where
_day - interval '3' DAY > CURRENT_DATE

both work

Roy Lambert