🚀 go-pugleaf

On Fri, 18 Dec 1998 17:58:28 -0800, glightsey@cari.net (Gerald Lightsey)
wrote:

>Given a field DOB in MYTABLE containing Date of birth, what would be the 
>proper local SQL syntax to obtain AGE?

In SQL (not just local SQL), a number of arithmetic operations can be
performed on DATE and TIMESTAMP type columns. For instance, subtracting a
smaller date from a larger date yields the difference between the two,
expressed in days. The statement below returns a value of 14,966 (there are
14,966 days between December 30, 1957 and December 21, 1998):

  SELECT ("12/21/1998" - "12/30/1957")
  FROM AnyTable

The number returned by the above query would be of type INTEGER (or
SMALLINT, if the difference is small enough). You could then use numeric
arithmetic available in SQL to translate from number of days to number of
years. One simplistic way would be to divide the number of days by 365.25.
In the query below, this yields a value of 40.97 (rounded to two decimal
places).

  SELECT (("12/21/1998" - "12/30/1957") / 365.25)
  FROM AnyTable

The problem with the preceding in local SQL is the unavailability of an
intrinsic function for retrieving the system date in an SQL statement.
Because of this, the current date needs to be written into the SQL
statement as a literal (when dynamically building the SQL statement) or
passed as a parameter.

  SELECT ((:CurrentDate - A.DOB) / 365.25)
  FROM AnyTable A

>What would be the syntax to obtain the first character of age (e.g. 3 for 
>anyone ages 30 to 39)?

In SQL, the function SUBSTRING is provided to retrieve part of a string
(CHAR) value. However, SUBSTRING works only on string type values and your
calculation results in a numeric type value. So you would need to take the
extra step of converting the numeric value to string using the CAST
function.

  SELECT SUBSTRING(CAST(((:CurrentDate - A.DOB) / 365.25) AS CHAR(20))
    FROM 1 FOR 1)
  FROM AnyTable A

//////////////////////////////////////////////////////////////////////////
Steve Koterski                            "Uhn!"
Technical Publications                    -- Ogg the caveman (c. 10,000BC)
INPRISE Corporation
http://www.inprise.com/delphi