Calculate Ages with Query/400
May 26, 2004 Hey, Ted
I love to read Four Hundred Guru. It always contains great ideas and techniques!
We need to calculate an age using Query/400. The database file that we are using has an eight-digit zoned-decimal birthdate field, in the format YYYYMMDD. Can you help?
You need to convert the zoned decimal field to a date field. Then you will be able to subtract it from the system date to get a date duration. A date duration is an eight-digit field representing years, months, and days. For more information about date durations, see my answer to Helen’s question.
The following query output, which was generated on May 26, 2004, shows age in two formats. The third column shows years, months, and days. The fourth column shows years only.
NAME DOB AGE AGEYEARS Betty 1981-01-01 23 years 04 months 25 days 23 Sue 1981-12-31 22 years 04 months 26 days 22 Bill 2000-05-25 4 years 00 months 01 days 4 Jack 2000-05-26 4 years 00 months 00 days 4 Jim 2000-05-27 3 years 11 months 30 days 3
Here are the result field calculations that I used to build those columns.
Select define result fields.
Field Expression Len Dec DOBCHAR digits(dob) DOBEDITED substr(dobchar,1,4)||'-'|| substr(dobchar,5,2)||'-'|| substr(dobchar,7,2) DOBDATE date(dobedited) AGE current(date) - dobdate AGEYEARS age / 10000 4 0
DOBCHAR converts the zoned-decimal date to character format. DOBEDITED puts the date into *ISO format (YYYY-MM-DD), and DOBDATE converts to the date data type. Subtracting DOBDATE from the system date yields a date duration, which you will need to edit in order to make it readable.
To edit the duration, select specify report column formatting, from the Query menu, move the cursor to the AGE field, press F16, select option 4, and enter this edit word:
' &years& &months& &days&'
There are four blanks before &years& and two blanks each before &months& and &days&.
I used simple division to extract the years portion of the duration. Since the months portion of the duration will never be more than 11, the number of years will never be rounded.