I am attempting to move Age calculations from the application to the DB - without success.

DateOfBirth is defined as a DATE column.
Added a new column AGE with a type of INTERVAL YEAR.

The calculation expression is CURRENT_DATE - DateOfBirth.

The error message is: (Expected NULL, SmallInt, BigInt, Interval or Year expression but instead found CURRENT_DATE - "DateOfBirth")

What am I doing wrong??

Many thanks..

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

Richard,

<< DateOfBirth is defined as a DATE column.
Added a new column AGE with a type of INTERVAL YEAR.

The calculation expression is CURRENT_DATE - DateOfBirth.

The error message is: (Expected NULL, SmallInt, BigInt, Interval or Year
expression but instead found CURRENT_DATE - "DateOfBirth")

What am I doing wrong?? >>

I'm sure Tim will answer this, but it seems to be some problems with the
implementation of interval expressions.

The correct SQL:2003 syntax would be:

(CURRENT_DATE - DateOfBirth) YEAR

AFAICS, ElevateDB doesn't enforce the required parentheses and interval
qualifier to be specified, but I'd would assume that the engine should
execute the standard syntax:

create table test
(
 DateOfBirth date,
 Age interval year generated always as (current_date - DateOfBirth) year
);

ElevateDB Error #700 An error was found in the statement at line 4 and
column 70
(Expected PRIMARY, UNIQUE, FOREIGN, REFERENCES, CHECK but instead found
year)

When removing the "year" qualifier from the column definition generation
clause, I'm getting the same error as you did:

ElevateDB Error #700 An error was found in the generated column expression
at
line 1 and column 17 (Expected NULL, SmallInt, Integer, BigInt, Interval or
Year
expression but instead found (CURRENT_DATE() - "DateOfBirth"))

I then checked the interval expression in a query:

create table test (DateOfBirth date);
insert into test values (date'2000-01-01');

select
 DateOfBirth,
 (current_date - DateOfBirth) year
from test

The query executes, but the result is weird:

DateOfBirth   year
-------------------------------
2000-01-01   226627200000

Additional problems:

select
 DateOfBirth,
 (current_date - DateOfBirth) year as Age
from test

ElevateDB Error #700 An error was found in the statement at line 6 and
column 37
(Expected FROM but instead found as)

select
 DateOfBirth,
 (current_date - DateOfBirth) year to month
from test

ElevateDB Error #700 An error was found in the statement at line 6 and
column 37
(Expected FROM but instead found to)

Ole Willy Tuv

Richard,

<< The calculation expression is CURRENT_DATE - DateOfBirth.

The error message is: (Expected NULL, SmallInt, BigInt, Interval or Year
expression but instead found CURRENT_DATE - "DateOfBirth") >>

The error is correct - you cannot assign a day-time interval, which is the
result of subtracting two dates (actually it's a day interval,
specifically), to a year-month interval.  However, Ole is correct in that we
need a way for you to specify that the result of the expression should be a
year interval.  We were relying on CAST() to do so, but CAST() has specific
rules that prohibit casting a day-time interval to a year-month inteval, and
vice-versa, since they involve two separate domains of time.  So, we're
going to have to implement a hard-specification for INTERVAL type
expressions according to the standard.

--
Tim Young
Elevate Software
www.elevatesoft.com

Ole,

<< AFAICS, ElevateDB doesn't enforce the required parentheses and interval
qualifier to be specified, but I'd would assume that the engine should
execute the standard syntax: >>

It doesn't.  See my response to Richard about CAST().

<< The query executes, but the result is weird:

DateOfBirth   year
-------------------------------
2000-01-01   226627200000 >>

Interval types show up in raw form in Delphi because there is no TFieldType
(or special TField) to distinguish them.  Internally, a year-month interval
is an Int32 in EDB and a day-time interval is an Int64.

<< select
 DateOfBirth,
 (current_date - DateOfBirth) year as Age
from test >>

Same as above - EDB doesn't use the interval specification on expressions.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< So, we're going to have to implement a hard-specification for INTERVAL
type expressions according to the standard. >>

Sounds good.

I think the reason why the standard requires an interval qualifer is to
remove any ambiguity/confusion about the return type of the expression.
Also, the parentheses are required since the interval qualifier is
qualifying the result of the subtraction and not the last operand.

Ole Willy Tuv

Ole,

<< I think the reason why the standard requires an interval qualifer is to
remove any ambiguity/confusion about the return type of the expression.
Also, the parentheses are required since the interval qualifier is
qualifying the result of the subtraction and not the last operand. >>

One thing that I'd like to point out, however, is that Richard's expression
still won't give him what he wants in terms of the age if expressed as
simply a year-month interval (which is what the YEAR specifier will do).
For example, my birthdate is 2007-03-12, which when subtracted from the
current date as expressed in year-months, will give him 444 months, or 37
years.  However, I'm not yet quite 37 (, so the age will be wrong.  I
think what he wants is the number of days divided by 365.25 (to account for
leap years) like this:

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

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:

CREATE TABLE "IntervalTable"
(
"DateOfBirth" DATE,
"Age" INTEGER COMPUTED ALWAYS AS
TRUNC ( CAST(CURRENT_DATE - DateOfBirth AS INTEGER) / 365.25)
)

The CAST to an INTEGER is required because intervals cannot contain
fractions, and so EDB won't allow an interval to be divided by a
floating-point number, only another integer.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

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

--
Best regards

Steve
"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:C76EC94F-D91F-44B0-BBFF-101F3B051557@news.elevatesoft.com...
> Ole,
>
> << I think the reason why the standard requires an interval qualifer is to
> remove any ambiguity/confusion about the return type of the expression.
> Also, the parentheses are required since the interval qualifier is
> qualifying the result of the subtraction and not the last operand. >>
>
> One thing that I'd like to point out, however, is that Richard's
> expression still won't give him what he wants in terms of the age if
> expressed as simply a year-month interval (which is what the YEAR
> specifier will do). For example, my birthdate is 2007-03-12, which when
> subtracted from the current date as expressed in year-months, will give
> him 444 months, or 37 years.  However, I'm not yet quite 37 (, so the
> age will be wrong.  I think what he wants is the number of days divided by
> 365.25 (to account for leap years) like this:
>
> 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)
> )
>
> 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:
>
> CREATE TABLE "IntervalTable"
> (
> "DateOfBirth" DATE,
> "Age" INTEGER COMPUTED ALWAYS AS
> TRUNC ( CAST(CURRENT_DATE - DateOfBirth AS INTEGER) / 365.25)
> )
>
> The CAST to an INTEGER is required because intervals cannot contain
> fractions, and so EDB won't allow an interval to be divided by a
> floating-point number, only another integer.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>

Tim

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>

Roy Lambert

Steve,

<< For example, my birthdate is 2007-03-12 >>

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!

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

--
Regards,
Jan Ferguson


Steve Forbes wrote:

<<Hi Tim,
<<
<<Happy birthday for Monday!! .. it's mine today
<<
<<--
<<Best regards
<<
<<Steve
<<"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in
<<message
<<news:C76EC94F-D91F-44B0-BBFF-101F3B051557@news.elevatesoft.com...
<<<<Ole,
<<<<
<<><< I think the reason why the standard requires an interval qualifer
<<is to  remove any ambiguity/confusion about the return type of the
<<expression.  Also, the parentheses are required since the interval
<<qualifier is  qualifying the result of the subtraction and not the
<<last operand. >>
<<<<
<<<<One thing that I'd like to point out, however, is that Richard's
<<<<expression still won't give him what he wants in terms of the age
<<<<if  expressed as simply a year-month interval (which is what the
<<<<YEAR  specifier will do). For example, my birthdate is 2007-03-12,
<<<<which when  subtracted from the current date as expressed in
<<<<year-months, will give  him 444 months, or 37 years.  However, I'm
<<<<not yet quite 37 (, so the  age will be wrong.  I think what he
<<<<wants is the number of days divided by  365.25 (to account for leap
<<<<years) like this:
<<<<
<<<<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) )
<<<<
<<<<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:
<<<<
<<<<CREATE TABLE "IntervalTable"
<<<<(
<<<<"DateOfBirth" DATE,
<<<<"Age" INTEGER COMPUTED ALWAYS AS
<<<<TRUNC ( CAST(CURRENT_DATE - DateOfBirth AS INTEGER) / 365.25)
<<<<)
<<<<
<<<<The CAST to an INTEGER is required because intervals cannot contain
<<<<fractions, and so EDB won't allow an interval to be divided by a
<<<<floating-point number, only another integer.
<<<<
<<<<--  Tim Young
<<<<Elevate Software
<<<<www.elevatesoft.com
<<<<
>>

Steve

Tim,

<< One thing that I'd like to point out, however, is that Richard's
expression still won't give him what he wants in terms of the age if
expressed as simply a year-month interval (which is what the YEAR specifier
will do). >>

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.

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

<< I think what he wants is the number of days divided by 365.25 (to account
for leap years) like this: >>

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

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 ?

The format of interval strings are quite similar to the format of a datetime
strings:

<year-month literal> ::=
   <years value> [ <minus sign> <months value> ]
   | <months value>

<day-time literal> ::=
    <day-time interval>
 | <time interval>

<day-time interval> ::=
   <days value> [ <space> <hours value> [ <colon> <minutes value>
   [ <colon> <seconds value> ] ] ]

<time interval> ::=
   <hours value> [ <colon> <minutes value> [ <colon> <seconds value> ] ]
 | <minutes value> [ <colon> <seconds value> ]
 | <seconds value>

Ole Willy Tuv