Re: How to calculate a person's age?

On Fri, 5 Sep 1997, Ing. Melvin Perez Cedano wrote: > I trying to calculate how old are a person. I have the birth date in a > column. I have been using a DATETIME YEAR TO YEAR variable in my program > and asigning to it the result of (TODAY - birthdate) UNITS YEAR it > returns NULL. [...] > How have you done this? Well, I hadn't had to do it, but the following seems to work: CREATE TABLE t ( d1 DATETIME YEAR TO DAY NOT NULL, d2 DATETIME YEAR TO DAY NOT NULL ); INSERT INTO t VALUES("1997-09-05", "1990-09-04"); INSERT INTO t VALUES("1997-09-05", "1990-09-06"); INSERT INTO t VALUES("1997-09-05", "1990-09-05"); -- Age in years version... SELECT d1, d2, EXTEND(d1, YEAR TO YEAR) - EXTEND(d2, YEAR TO YEAR) age FROM t WHERE EXTEND(d1, MONTH TO DAY) >= EXTEND(d2, MONTH TO DAY) UNION SELECT d1, d2, EXTEND(d1, YEAR TO YEAR) - EXTEND(d2, YEAR TO YEAR) - 1 UNITS YEAR age FROM t WHERE EXTEND(d1, MONTH TO DAY) < EXTEND(d2, MONTH TO DAY); -- Output d1|d2|age DATETIME YEAR TO DAY|DATETIME YEAR TO DAY|INTERVAL YEAR(4) TO YEAR 1997-09-05|1990-09-04|7 1997-09-05|1990-09-05|7 1997-09-05|1990-09-06|6 -- Years and months version... SELECT d1, d2, EXTEND(d1, YEAR TO MONTH) - EXTEND(d2, YEAR TO MONTH) age FROM t WHERE EXTEND(d1, DAY TO DAY) >= EXTEND(d2, DAY TO DAY) UNION SELECT d1, d2, EXTEND(d1, YEAR TO MONTH) - EXTEND(d2, YEAR TO MONTH) - 1 UNITS MONTH age FROM t WHERE EXTEND(d1, DAY TO DAY) < EXTEND(d2, DAY TO DAY); -- Output d1|d2|age DATETIME YEAR TO DAY|DATETIME YEAR TO DAY|INTERVAL YEAR(4) TO MONTH 1997-09-05|1990-09-04|7-00 1997-09-05|1990-09-05|7-00 1997-09-05|1990-09-06|6-11 It's a pity that a UNION is necessary -- you could use an SP to do the calculation with an IF clause (and XPS already has the SQL-92 CASE which would allow it to be done inline, and CASE will be added to forthcoming versions of IUS -- and ODS as far as I know). Yours, Jonathan Leffler (johnl@informix.com) #include <witticism.h>