Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 8 of 8 total |
Calculating AGE |
Wed, Apr 11 2007 8:30 PM | Permanent Link |
Richard Harding | 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 |
Thu, Apr 12 2007 4:17 AM | Permanent Link |
"Ole Willy Tuv" | 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 |
Thu, Apr 12 2007 4:36 PM | Permanent Link |
"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 |
Thu, Apr 12 2007 3:44 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Fri, Apr 13 2007 12:09 AM | Permanent Link |
Richard Harding | 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 |
Fri, Apr 13 2007 3:10 AM | Permanent Link |
"Ole Willy Tuv" | 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 |
Fri, Apr 13 2007 3:38 AM | Permanent Link |
"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 |
Mon, Apr 16 2007 1:55 AM | Permanent Link |
Richard Harding | 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 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |