Tim,

<< CREATE TABLE "IntervalTable"
(
"DateOfBirth" DATE,
"Age" INTEGER COMPUTED ALWAYS AS
TRUNC ( CAST( CAST( CAST(CURRENT_DATE - DateOfBirth AS INTERVAL DAY) AS
VARCHAR(10)) AS INTEGER) / 365.25)
) >>

If the interval type is implemented correctly, converting a single-field
interval to integer should be as easy as a plain cast:

cast((current_date - DateOfBirth) year as integer)

Example:

cast((date'2007-03-09' - date'1990-01-01') year as integer)

should return the value 17.

The result of the expression:

(date'2007-03-09' - date'1990-01-01') year

is equivalent to:

interval'17' year

The cast specification is therefore equivalent to:

cast(interval'17' year as integer)

which should also return the value 17.

Ole Willy Tuv

Steve,

<< Happy birthday for Monday!! .. it's mine today >>

Well, it's a well-known fact that only cool people have birthdays in March.


--
Tim Young
Elevate Software
www.elevatesoft.com

Jan,

<< Tim is a MUCH better person than we thought! Imagine all he has done with
DBISAM and EDB and he *WILL* be born on Monday! >>

Geez, sometimes I think I need a helper just to check for some of the stupid
mistakes I make when I post here.

<< Happy Birthday Tim!! (you too Steve...) >>

Thanks, although I'm not liking getting into the latter part of my 30's
already.

--
Tim Young
Elevate Software
www.elevatesoft.com

Roy,

<< I do hope you're taking the day off on the 12th, or was that just a
subtle hint to say "you've just got time to get my pressy in the post" <vbg>
>>

Yeah, I'll only be here a little bit on Monday.

--
Tim Young
Elevate Software
www.elevatesoft.com

Ole,

<< If the interval type is implemented correctly, converting a single-field
interval to integer should be as easy as a plain cast: >>

Did you read the last part of my message ?

"The extra CAST to the string is to get around an issue with using INTERVAL
values straight-up as an integer value.  After 1.01 Build 2, this will
suffice:"

--
Tim Young
Elevate Software
www.elevatesoft.com

Ole,

<< I would expect a YEAR expression to return the year fraction of the
interval and a YEAR TO MONTH expression to return both the year and month
fractions. >>

Yes, but neither of them support a fraction of a month.  Essentially, the
lowest unit of measure in a year-month interval is a month, so that's all we
can use for calculations.

<< I checked the following expressions with Mimer SQL (which implements the
INTERVAL type and interval arithmetic):

select (date'2007-03-09' - date'1990-01-01') year

returns 17

select (date'2007-03-09' - date'1990-01-01') year to month

returns 17-02 >>

Which is what EDB returns also (with the new expression value syntax).  Of
course, again, the issue with Delphi not knowing anything about interval
types causes both to be returned as 206 (months) unless the result is cast
into a varchar.

<< This is what I'd expect the "(CURRENT_DATE - DateOfBirth) YEAR"
expression to return. >>

See above.  There's no way, given a year-month interval, to return a result
that is accurate down to the day.  The two are simply not compatible.  Sure,
we could fudge things internally and do a division by 365.25 days (or
something similar) in the particular case of a day interval being converted
to a year interval, but I'm not sure if that is good idea since it gives the
impression that day-time intervals can be converted to and from year-month
intervals, and they can't in all/most cases.

<< I guess it's not easy to implement interval types and arithmetics
properly using the VCL, since there's no support for it in the VCL. Would it
be possible to use a string field to represent interval values in TDataSet,
and do the neccessary calculations and conversions internally ? >>

Yes, but there are some pretty sticky issues interms of implementing new
field types in Delphi, especially with respect to the TFieldType
enumeration.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< Yes, but neither of them support a fraction of a month.  Essentially, the
lowest unit of measure in a year-month interval is a month, so that's all we
can use for calculations. >>

Sure, but the two types have different precision. A YEAR interval has one
field, the year fraction, while a YEAR TO MONTH interval has two fields, the
year fraction and the month fraction. The interval between two dates,
represented in months, can therefore be different values depending on the
interval qualifier.

<< Which is what EDB returns also (with the new expression value syntax).
Of course, again, the issue with Delphi not knowing anything about interval
types causes both to be returned as 206 (months) unless the result is cast
into a varchar. >>

Which is not correct. The interval (date'2007-03-09' - date'1990-01-01')
year equals 204 months, while the interval (date'2007-03-09' -
date'1990-01-01') year to month equals 206 months.

<<< This is what I'd expect the "(CURRENT_DATE - DateOfBirth) YEAR"
expression to return. >>>

<< See above.  There's no way, given a year-month interval, to return a
result that is accurate down to the day.  >>

What I meant was that the expression (CURRENT_DATE - DateOfBirth) YEAR
should return the number of years between the two dates as an interval, just
like your expression returns the number of years as an integer. Richard's
original Age column was an INTERVAL YEAR type, and the expression
(CURRENT_DATE - DateOfBirth) YEAR should therefore be an appropriate
assignment source for the column.

<< The two are simply not compatible. >>

Agree, year-month and day-time intervals are not compatible and should not
be mixed. However, this is not the issue here, since the intervals discussed
in this thread are year-month intervals.

Ole Willy Tuv

Tim,

<<< Which is what EDB returns also (with the new expression value syntax).
Of course, again, the issue with Delphi not knowing anything about interval
types causes both to be returned as 206 (months) unless the result is cast
into a varchar. >>>

<< Which is not correct. The interval (date'2007-03-09' - date'1990-01-01')
year equals 204 months, while the interval (date'2007-03-09' -
date'1990-01-01') year to month equals 206 months. >>

Just for clarity:

cast((date'2007-03-09' - date'1990-01-01') year as interval month) should
return 204.

cast((date'2007-03-09' - date'1990-01-01') year to month as interval month)
should return 206.

cast((date'2007-03-09' - date'1990-01-01') year as varchar(30)) should
return the string INTERVAL'17'YEAR.

cast((date'2007-03-09' - date'1990-01-01') year to month as varchar(30))
should return the string INTERVAL'17-02'YEAR TO MONTH.

Ole Willy Tuv

Happy Birthday, Steve & Tim.

I am glad that we have all of that sorted out.  Thank you Ole & Tim.


--
Richard Harding
Windella Computer Knowhow
28 Freeman Drive
Lochinvar NSW 2321
Phone:    61 2 4930 7336
Mobile:    0419 016 032
email:    rharding@wck.com.au

Ole,

<< Just for clarity:

cast((date'2007-03-09' - date'1990-01-01') year as interval month)
should return 204.

cast((date'2007-03-09' - date'1990-01-01') year to month as interval month)
should return 206. >>

Yes, but I'm not arguing about the above calculations.

<< cast((date'2007-03-09' - date'1990-01-01') year as varchar(30)) should
return the string INTERVAL'17'YEAR.

cast((date'2007-03-09' - date'1990-01-01') year to month as varchar(30))
should return the string INTERVAL'17-02'YEAR TO MONTH. >>

Well, EDB doesn't return the INTERVAL or YEAR/YEAR TO MONTH keywords, only
the actual literal value itself, just like it doesn't return DATE
'<DateLiteral>' when casting DATEs to VARCHARs.

--
Tim Young
Elevate Software
www.elevatesoft.com