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?

–Greg

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.

–Ted