Tim,

The AGE is being calculated correctly but when I get to the AgeAtVisit, I receive the message

ElevateDB Error #700 An error was found in the statement at line 9 and column 30
(Expected Date, Interval Year, Interval Year To Month or Interval Day
expression but instead found "EpisodeDate")

SELECT
  Client.ID,
  Client.LastName,
  Client.FirstName,
  Client.DateOfBirth,
  ClientEpisode.ID,
  ClientEpisode.EpisodeDate,
  FLOOR(CAST(CURRENT_DATE - DateOfBirth AS INTEGER) / 365.25) AS Age,
  FLOOR(CAST(CURRENT_DATE - EpisodeDate AS INTEGER) / 365.25) AS AgeAtVisit
FROM
  Client
     INNER JOIN ClientEpisode
        ON Client.ID = ClientEpisode.ClientID

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

Is the column type of EpisodeDate DATE ?

The expression (date_value1 - date_value2) returns an interval and should
normally require an interval qualifier.

1.02 b1 calculates intervals correctly, so you can get the integer
representation of your Age and AgeAtVisit expressions with the following
syntax:

CAST((CURRENT_DATE - DateOfBirth) YEAR AS INTEGER) AS Age
CAST((CURRENT_DATE - EpisodeDate) YEAR AS INTEGER) AS AgeAtVisit

Ole Willy Tuv

Richard,

<< 1.02 b1 calculates intervals correctly, so you can get the integer
representation of your Age and AgeAtVisit expressions with the following
syntax:

CAST((CURRENT_DATE - DateOfBirth) YEAR AS INTEGER) AS Age
CAST((CURRENT_DATE - EpisodeDate) YEAR AS INTEGER) AS AgeAtVisit >>

Provided that the DateOfBirth and EpisodeDate column type is DATE. If the
column type is TIMESTAMP, the expressions return null in 1.02 b1.

Ole Willy Tuv

Ole,

<< Provided that the DateOfBirth and EpisodeDate column type is DATE. If the
column type is TIMESTAMP, the expressions return null in 1.02 b1. >>

I will check this out.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thanks Tim. . .

The EpsiodeDate is a TIMESTAMP field and DateOfBirth is a DATE field.

The following function works OK:   EXTRACT(YEAR FROM EpisodeDate)

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

<< The EpsiodeDate is a TIMESTAMP field and DateOfBirth is a DATE field. >>

In that case, the following expressions should give you what you want:

SELECT
  Client.ID,
  Client.LastName,
  Client.FirstName,
  Client.DateOfBirth,
  ClientEpisode.ID,
  ClientEpisode.EpisodeDate,
  CAST((CURRENT_DATE - DateOfBirth) YEAR AS INTEGER) AS Age,
  CAST((CURRENT_TIMESTAMP - EpisodeDate) YEAR AS INTEGER) AS AgeAtVisit
FROM
  Client
     INNER JOIN ClientEpisode
        ON Client.ID = ClientEpisode.ClientID

Ole Willy Tuv

Richard,

This will also return what you want:

SELECT
  Client.ID,
  Client.LastName,
  Client.FirstName,
  Client.DateOfBirth,
  ClientEpisode.ID,
  ClientEpisode.EpisodeDate,
  FLOOR(CAST(CURRENT_DATE - DateOfBirth AS INTEGER) / 365.25) AS Age,
  FLOOR(CAST(CURRENT_DATE - CAST(EpisodeDate AS DATE) AS INTEGER) / 365.25)
AS AgeAtVisit
FROM
  Client
     INNER JOIN ClientEpisode
        ON Client.ID = ClientEpisode.ClientID

But, there's no need to do the interval math explicitly in the select list
expressions, since EDB 1.02 b1 and later does the correct calculation
internally (according to the specified interval qualifier).

Ole Willy Tuv

Thanks Ole.

It is much appreciated.

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